How Did You Get Here?

So... someone was running a query on PROD via Microsoft Access that was blocking  the main applications. For now, ignore all the red flags that statement fires off in your head...

You find all their info using sp_whoisactive. However when you go to the SQL Server Security Logins AND the Database Security Users sections in SSMS their Active Directory user is no where to be found.

They must be getting access via an Active Directory Group. So you use the xp_logininfo system stored procedure

exec xp_logininfo '<domain>\<username>'

But that shows no records... the plot thickens.

If you're lucky you have a robust ticketing system that everyone uses and just a couple of AD groups on the SQL server. So then all it would take is a quick search through it to see that one of the other administrators decided to rename an AD group and you can find it quickly in your ticketing system.

Otherwise, you'll have to dig though the bowels of AD to match up the SIDs with the ones in the Server_Principals security catalog view.

Or use a little powershell. This requires that you have a couple of things setup:

1. The dbatools module installed
2. A Central Managment Server configured via SSMS

On to the code:

#Set your domain here:

$domain = ''

Get-DbaRegisteredServer -SqlInstance $cmsServer |  # Get your the list of servers from your SQL CMS
    Sort-Object name -Unique | # Filter out duplicates
    ForEach-Object { #Loop through each server
        $server = $PSItem 
        Write-Verbose $server.name -Verbose    

        Get-DbaLogin -SqlInstance $server | # Get all the logins on that server
            Where-Object logintype -EQ 'WindowsGroup'   | 
            Where-Object name -NotMatch 'NT SERVICE'    |
            Where-Object name -NotMatch 'NT AUTHORITY'  |
            Where-Object name -NotMatch 'BUILTIN'       |
            Sort-Object  name |
            ForEach-Object {
                $login = $PSItem
                $sid = New-Object Security.Principal.SecurityIdentifier $login.get_sid() , 0
        
                $data += 
                    [PSCustomObject]@{
                        SqlInstance = $login.SqlInstance
                        SQLLogin = $login.name
                        SID = $sid.value
                        ADGroup = "$domain\$((Get-ADGroup $sid).name)"
                    }
            }
    }

    $data | 
        Where-Object {$_.sqllogin -ne $_.adgroup} |
        #Where-Object name -NotMatch 'NT AUTHORITY' |
        Where-Object adgroup -NE "$domain\" |  #Commment this out to see the ones that truly no longer exist in the domain
        Sort-Object SqlInstance, SQLLogin |
        #Group-Object adgroup | select name, count |
        ft -AutoSize

Ok so now how do you fix it ? Because I wasn't brave enough to just fix them all in one fail swoop (this returned over 50 of them in my environment) I limited my repair script to just one sever at a time. Feel free to do them all at once if you're feeling lucky...

$serverName = "<servername>"

$data |
    Where-Object sqlinstance -eq $serverName |
    Where-Object {$_.sqllogin -ne $_.adgroup} |        
    Sort-Object SqlInstance, SQLLogin |
    ForEach-Object {
        $o = $psitem
        $l = Get-DbaLogin -SqlInstance $o.SqlInstance -Login  $o.SQLLogin
        
        #rename the login        
        $l.Rename($o.ADGroup)        

        #rename the database users
        Get-DbaDatabase -SqlInstance $o.SqlInstance -OnlyAccessible |
            Where-Object ReadOnly -EQ $false |
            Sort-Object -Property @{e = {$_.IsSystemObject}; Ascending = $false}, Name |
            ForEach-Object {
                $db = $PSItem
                Write-Verbose "$($o.SqlInstance) - $($db.name)" -Verbose
                
                $user = 
                    $db.Users | Where-Object name -eq $o.SQLLogin 

                IF (-not ([string]::IsNullOrEmpty($user))) {
                    Write-Verbose "`t $($user.name)" -Verbose
                    $user.Rename($o.ADGroup)
                }
            }
    }