asked on

Permissions for User Information

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

Ryan McCauley

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

Declan Basile

ASKER

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?

ASKER CERTIFIED SOLUTION

Ryan McCauley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Declan Basile

ASKER

I got it working. Thank you.