Cleanup SQL Server Logins

Cleaning up after someone leaves. We all do it right? Our companies all have water tight policies and procedures to handle that, or maybe they have policies but not procedures. Or maybe they just kind of don't.

Well, if your company is anything like most of the places I've worked, it's somewhere in the middle. And from a DBA perspective, especially on non-production environments, your list of logins and users becomes a bit long and messy after a while.


I've put together the PowerShell script below to get a list of Active Directory accounts as SQL logins and then checks for their existence in Active Directory. The script then generates SQL to drop users and logins. The SQL is not executed automatically in order to allow you to confirm whether those users and logins really can be dropped.

The script can check multiple SQL instances at once. Either by interrogating an asset database. My company uses Landesk. Alternatively, a list of instance names can be entered manually.

In order for the script to run successfully, the user must have permissions  and access to all the SQL Server instances listed and read permissions on the Active Directory server. You'll also need to have the Powershell Active Directory module and SQLPS installed on the client machine. To install the Active Directory module see:

• Windows 10: Install the Active Directory PowerShell Module on Windows 10
• Windows 7:   How to add Active Directory module in PowerShell in Windows 7

UPDATE: Based on some feedback I've altered the script to use the Windows login's SID instead of name. This allows for any name changes. I've also added a Domain parameter so that if you have users from other trusted domains the script will only find the users from your preferred domain. By default the script uses the $env:USERDOMAIN environment variable. But the parameter can be changed as needed.

cls

$ADServer = "ADServer01"
$Domain = $env:USERDOMAIN
$opt = (Get-Host).PrivateData
$opt.WarningForegroundColor = "DarkRed"
$opt.WarningBackgroundColor = "Yellow"
$opt.VerboseForegroundColor = "White"
$opt.VerboseBackgroundColor = "Green"

<#### LANDESK QUERY ####>
#$Servers = (Invoke-Sqlcmd -ServerInstance LandeskServer -Database Landesk -Query "SELECT DeviceName from computer where DeviceName like '%sql%D' and  DomainName like 'DomainName%'").DeviceName
<#### LANDESK QUERY ####>

<#### MANUAL SERVER LIST ####>
[string[]]$Servers = ("SQLServer01","SQLServer02")
<#### MANUAL SERVER LIST ####>

foreach ($Server in $Servers)
{
    $FailedUsers = ""
    $DropUserQuery = ""
    Try
    {
        
        $users = (Invoke-Sqlcmd -ServerInstance $Server -Database master  -QueryTimeout 0 -ErrorAction SilentlyContinue -Query "
                    DECLARE @users table (Name sysname)
                    declare @command varchar(4000)
                                              set @command = 'USE [?];
                    insert into @users 
                    select substring(name,charindex(''\'',name,1)+1,500) as Name
                    from sysusers
                    where isntuser = 1
                            and name like ''$Domain%''
                            and name not like ''NT %'''
                    insert into @users 
                    select substring(name,charindex('\',name,1)+1,500) as Name
                            from syslogins
                            where isntuser = 1
                            and name like '$Domain%'
                            and name not like 'NT %'
                    select distinct Name from @users").Name
        foreach ($user in $users)
        {
            Try
            {
                $query = "select sid as SID from syslogins  where name = '$Domain\$user' and isntuser = 1" 
                $SID = Invoke-Sqlcmd -ServerInstance $Server -Database master -Query $query | foreach {new-object security.principal.securityidentifier($_.SID,0)};
                $results = Get-ADUser -Server $ADServer -Filter {SID -eq $SID} #| select SAMAccountName 
                Write-Verbose $results.SamAccountName
            }
            Catch
            {
                $FailedUsers = "$FailedUsers`n$user"
                $query = "declare @command varchar(4000)
                          set @command = 'USE [?]
                          if exists (select 1 from sys.sysusers where isntname = 1 and name = ''$Domain\$user'')
                          select ''USE [?];' + CHAR(10) + 'DROP USER ['' + name + '']'  + CHAR(10) + '''
                          from sys.sysusers
                          where isntname = 1
                          and name like ''%$user'''
                          exec sp_msforeachdb @command"
                $DropUserQuery = "$DropUserQuery`n" + (Invoke-Sqlcmd -ServerInstance $Server -Database master -Query $query).Column1
                $query = "USE [master];`n select 'DROP LOGIN [' + name + ']'  + CHAR(10) + ''
                          from syslogins
                          where name like '%$user'"
                $DropUserQuery = "$DropUserQuery`n" + (Invoke-Sqlcmd -ServerInstance $Server -Database master -Query $query).Column1
            }
        }

         if ($FailedUsers.length -gt 0) 
        {
            Write-Warning "On server $server : The following $Domain users do not exist in Active Directory: $FailedUsers"
            Write-Host "On server $server : Run the following SQL to remove the users as required `n $DropUserQuery"
        }
        else
        {
            Write-Verbose "On server $server : All $Domain users were found in Active Directory`n`n" -Verbose
        }
    }
    Catch
    {
        Write-Warning "Cannot connect to $Server`n`n"
    }    
}