We have some of our production servers in a domain that does not trust our corporate domain, so I need to use SQL logins to connect with these servers. One of the servers supports an application that has many databases, all structurally alike, but each for their own 'client' data. Due to some personnel changes I had to remove some users and add a number of additional ones. Now, adding a user to each of over 100 databases, and enabling the db_datareader database role for the user in each database. For one user alone that's a tedious process, but imagine doing four or five users. Needless to say, SMO comes to the rescue once again.
The first thing I did (outside the SMO application) was add the login for each user. That part's easy in Management Studio. The rest I did in the application. First is establishing the connection to the server, and instantiating the object for the login, because we're going to reset the default database after granting the database access. (Notice the use of a SQL login to connect, as it's not in the corporate domain.)
Dim srvMgmtServer As Server
Dim strUser As String
Dim objLogins As LoginCollection
Dim objLogin As Login
strUser = "juser"
srvMgmtServer = New Server()
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.ServerInstance = "MyServer"
srvConn.LoginSecure = False
If srvConn.LoginSecure = False Then
srvConn.Login = "remadmin"
srvConn.Password = "pas$w0rd"
End If
objLogins = srvMgmtServer.Logins
objLogin = objLogins.Item(strUser)
All the databases used by this app are prefixed by either va_ or vb_, but the 'boilerplate' databases start with app_default, and the application master database is called app_master. Once we've established that the database qualifies and is not a snapshot database, we then attempt to instantiate the user object for that user. If it doesn't exist we create it. Then we check to see if it has the db_datareader role assigned to it, and if not, do so.
' Browse the database collection on the target server
Dim lisDBs As DatabaseCollection
lisDBs = srvMgmtServer.Databases
Dim objDB As Database
For Each objDB In lisDBs
If Not objDB.IsDatabaseSnapshot Then
If Left(objDB.Name, 3) = "va_" Or Left(objDB.Name, 3) = "vb_" Or _
Left(objDB.Name, 11) = "app_default" Or objDB.Name = "app_master" Then
Dim objUsers As UserCollection
Dim objUser As User
objUsers = objDB.Users
objUser = objUsers.Item(strUser)
If (objUser Is Nothing) Then
objUser = New User(objDB, strUser)
objUser.Login = strUser
objUser.Create()
End If
If Not objUser.IsMember("db_datareader") Then
objUser.AddToRole("db_datareader")
objUser.Alter()
End If
End If
End If
Next
Lastly, once we're certain the user has access to the app_master database we set the login's default database to the app_master.
objLogin.DefaultDatabase = "app_master"
objLogin.Alter()
These few lines of code saved hours of tedium, and I'm also certain I would have missed a database or two along the way.
Allen