In my shop we have a lot of web sites, each using their own SQL Server database. Many of these sites are for conferences and often once the conference is over the site is no longer needed. As a result my servers have had a lot of databases created, logins created, then databases deleted, but the logins don't always get deleted because developers will use existing logins for new sites (sometimes), so I'm not always sure what logins can go.
Over time this gets a bit ugly, so I decided to write a program using SMO to evaluate a server and create a script to drop logins that haven't been assigned rights in any existing database. First I created a couple of classes, one for Users and one for Logins:
Public Class dbUser
Public name As String
Sub New(ByVal newName As String)
name = newName
End Sub
End Class
Public Class dbLogin
Public name As String
Sub New(ByVal newName As String)
name = newName
End Sub
End Class
The first thing I did was connect to the server, and I've posted that code before so I won't do so again, but then defined some variables as follows:
Dim strUserNm As String 'String to hold the user name
Dim colUsers As New Collection 'Server-wide collection of users
Dim strLoginNm As String 'String to hold the login name
Dim colLogins As New Collection 'Server-wide collection of logins
Dim dbColl As DatabaseCollection 'SMO Database Collection
Dim dbCurr As Database 'SMO Database object
Dim logColl As LoginCollection 'SMO Login Collection
Dim logCurr As Login 'SMO Login Object
Dim objUser As dbUser 'Instance of the User Class
Dim objLogin As dbLogin 'Instance of the Login Class
Dim file As System.IO.StreamWriter 'File to receive the output script
Now, I loop through the databases on the server, and if I haven't already added the current user to a server-wide collection of users, I add it. Then, I loop through the server logins collection, populating my collection of logins.
dbColl = srvMgmtServer.Databases 'Get the collection of databases from the server
For Each dbCurr In dbColl 'Loop through the databases
Dim colDBUsers As UserCollection 'SMO User Collection
Dim usrDBUser As User 'SMO User Object
colDBUsers = dbCurr.Users 'Get the collection of users for the database
For Each usrDBUser In colDBUsers 'Loop through the users
strUserNm = usrDBUser.Name 'Get the users name
If Not (colUsers.Contains(usrDBUser.Name)) Then 'Test to see if the user is already in the collection
colUsers.Add(New dbUser(strUserNm), strUserNm) 'If not, add it
End If
Next
Next
logColl = srvMgmtServer.Logins 'Get the collection of logins for the server
For Each logCurr In logColl 'Loop through the logins
strLoginNm = logCurr.Name 'Get the name of the login
colLogins.Add(New dbLogin(strLoginNm), strLoginNm) 'Add it to the collection
Next
Now I remove from my logins collection every value that exists in the server-wide users collection, effectively leaving me with a collection of logins with no database access. (This isn't entirely true - I could have server-level logins in here, and do.
For Each objUser In colUsers 'Loop through the Users collection
If colLogins.Contains(objUser.name) Then 'See if it exists in the logins collection
colLogins.Remove(objUser.name) 'Remove it if it does
End If
Next
Finally I write the names in the logins collection to a file, building it as a SQL Script, which I can run on the server when I'm ready:
file = My.Computer.FileSystem.OpenTextFileWriter("C:\CleanLogins.sql", True)
For Each objLogin In colLogins 'Loop through the remaining logins
file.WriteLine("drop login " + objLogin.name) 'Write the DROP LOGIN line
file.WriteLine("go") 'Each drop is in its own batch
Next
Before I run the script I review it to remove logins I know need to remain, but I've automated the process of knowing what logins are no longer used, improving the security of my servers.
Allen