Permissions for User Information

Declan_Basile
Declan_Basile used Ask the Experts™
on
I recently upgraded SQL Server Express 2005 running on a Server 2003 Server to SQL Server Express 2008 running on a Server 2008 Server.  I can still access the tables in the databases in SQL Server from a VB program via a connection string.  However, there is code in the VB program that loops through users of a given database and determines if the user is a member of a database role named "ExecuteCalibrationUpdateSPs" using the "IsMember" method.  This code no longer works.  Is there something I need to do to give the program the permission to loop through the users and find out what roles they are members of?  The VB code is ...

        Dim u As User
        Dim u1 As New User
        For Each u In db.Users
            If u.Login = Userid Then
                Me.UserName = u.Name
                u1 = u
                Exit For
            End If
        Next u
        ' Is the user member of role?
        If u1.IsMember("ExecuteCalibrationUpdateSPs") Then
           MyIsMember = True
        End If
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan McCauleySenior Data Architect

Commented:
Have you upgraded to the SQL 2008 SMO objects? There are a few differences between the 2005 and 2008 SMO spaces, and even though most functions don't change names, I've run into a number of issues using SMO to target a database of a different version.

The downloads can be found under "Microsoft SQL Server 2008 Management Objects" here:

http://www.microsoft.com/download/en/details.aspx?id=3522

Author

Commented:
I wasn't the person who created the VB program.  I write programs in Microsoft Access using a SQL Server database, but I am not very familiar with VB.  The code in the VB program has the following Imports ...

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Agent
Imports Microsoft.SqlServer.Server
Imports Microsoft.SqlServer.Management.Common
Imports System.Data.SqlTypes

I get warnings that the 1st, 2nd, and 4th imports can't be found.  I installed SQL Server Express 2008 on the same computer as the VB program.  The folder "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies" contains the file "Microsoft.SqlServer.Smo.dll" (Note: without the ".Management").  It finds "Microsoft.SqlServer.Server" and "System.Data.SqlTypes" even though they aren't in that folder.  What folder are these namespaces in?  Do I still need to download the management objects or should I already have them from installing SQL Server 2008 Express on my computer?
Senior Data Architect
Commented:
You should find those files in the .NET GAC (Global Assembly Cache), located at C:\Windows\Assembly (unless you installed Windows somewhere else).

In the GAC, you can also view the installed version of the assemblies - it's shown as a column in the detail view. I suspect you've got the 9.X versions (SQL 2005) installed, but since you moved to SQL 2008, that works best with the 10.X versions of the assemblies. Unfortunately, the compiled EXE targets a particular version of these assemblies, so you can't change it without recompiling the source.

You can try adding the references to the 2008 SMO versions of the DLLs in the app.config file, but I'm not sure how well that will work, since the EXE has internal signatures of the 2005 versions when it was compiled. If you have the source, then changing the version of the DLLs that are referenced and recompiling is the easiest way to go.

Author

Commented:
I got it working.  Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial