Tame Your Vicious SIDs

If you're like me you've inherited an established environment with multiple servers. Most of the servers in this environment are using Mixed Mode authentication with several SQL Server Logins, as well as Windows Auth logins. What I've found is several of these SQL logins were created across multiple servers manually, without bringing the hashed SID across with them. This could have been done manually using the CREATE LOGIN from a SID t-sql syntax or even easier the Copy-DbaLogin command form dbatools

Step #1: You can't fix what you don't measure

$userName = 'TonyW'
$logins = @()

Get-DbaRegisteredServer -SqlInstance 'OurCMSServer' |
     sort-object -unique |
     ForEach-Object {
        write-verbose $psitem.name -verbose
        try {
            $s = Connect-DbaInstance $PSItem
        }
        catch { return; }

        if ($s.logins[$username]) {

            $logins += 
                [pscustomobject]@{
                    Server = $psitem
                    Version = $s.Version
                    Login = $username
                    Sid = 
                        ($s.logins[$username].Get_Sid() | 
                            ForEach-Object { ("{0:X}" -f $_).PadLeft(2, "0") }
                        ) -join ""
                }            
        }
    }

$logins |     
    sort-object SID, Server |
    ft -AutoSize -GroupBy SID

Step #2: Fixing it the hard way...

1. On the Source server get the:
• Current SID
2. On the Destination server:
1. Collect the following information
• Hashed password
• Databases owned by login
• SQL Agent Jobs owned by login
• Server roles the login is a member of
• Server permissions for the login
2. Kill existing connections and drop the login
3. Recreate the login using the SID from the source server and the hashed password from the Destination server
4. Reapply the following
• Database ownership
• SQL Agent Job ownership
• Server Role memberships
• Server Permissions
5. Repair orphaned database users.

Step #3 Fixing it the easy way
Stay tuned to see how to do all this with PowerShell (I promise it won't be as long as it was between the previous post and this one).