Allen White

Cleaning up abandoned logins

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