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).

Wrapper for DBAChecks

Working with DBAChecks, I was wanting a bit more control as well as some metrics on the execution. Here's what I came up with:

Generic variable initialization:

$ExcludeServer = @()
$ExcludeGroup = @()
$timeData = @()

$PowerBiDataPath = '\\Shared Network path for the data\dbachecks\data'
$cmsServer = "My CMS Server"

Defining the exclusions for the tests. I wanted to be able to exclude specific tags on a per server or ServerGroup level.

Class Exclusion
{
    [ValidateNotNullOrEmpty()][String]$Name
    [string[]]$Tags
}
$ExcludeGroup += [Exclusion]@{name="DBA Sandboxes"} #Exclude entire group
$ExcludeGroup += [Exclusion]@{name="Dev"; tags=@("Backup")} # Exclude backup checks on the Dev group

Excluding the servers based on a wildcard, in this case, it's my DR servers.

#Exclude the DR servers
Get-DbaRegisteredServer $cmsServer |     
    where-object {$PSItem.name -Like '*-DR' -or $PSItem.name -Like '*-DR-*'} | 
    Sort-Object -Unique | 
    ForEach-Object {
        $ExcludeServer += [Exclusion]@{name = $PSItem.name}
    }

Getting the list of groups to test from the CMS server

#Waiting for this to get added to dbatools.io
#(Get-DbaRegisteredServerGroup -SqlInstance $cmsServer -ExcludeGroup $ExcludeGroup).name | sort-object -Unique
$groups = ((Get-DbaRegisteredServersStore -SqlInstance $cmsServer).DatabaseEngineServerGroup.ServerGroups).name | 
    Where-Object {$PSItem -notin ($ExcludeGroup | where Tags -eq $null).name} |
    Sort-Object -Unique      

Define the tags that you want to test for

$tags = 'AutoClose', 'AutoShrink', 'DAC', 'TempDbConfiguration', 'DatafileAutoGrowthType' 

Here's the real meat of this script...

$groups |    
    ForEach-Object {
        $groupName = $PSItem

        # Get the list of SQL instances from the CMS sever, except those that are excluded
        $sqlinstances = Get-DbaRegisteredServer -SqlInstance $cmsServer -Group $groupName | 
            where name -NotIn ($ExcludeServer | where Tag -eq $null ).name    

        $tags | ForEach-Object {
            $tag = $PSItem

            $obj =  [pscustomobject]@{            
                ServerGroup = $groupName
                NumServers = $sqlinstances.Count
                Tag = $tag
                InvokeStartTime = Get-Date
                InvokeCompleteTime = $null
                WriteResultsTime = $null
                TestExecution = $null
                InvokeDuration = $null
                ResultsDuration = $null
                PassedCount       = $null
                FailedCount       = $null
                SkippedCount      = $null
                PendingCount      = $null
                InconclusiveCount = $null                
            }
            
            $results = Invoke-DbcCheck -SqlInstance $sqlinstances -tags $tag -PassThru -Show Fails
            $obj.TestExecution     = $results.time
            $obj.PassedCount       = $results.PassedCount      
            $obj.FailedCount       = $results.FailedCount      
            $obj.SkippedCount      = $results.SkippedCount     
            $obj.PendingCount      = $results.PendingCount     
            $obj.InconclusiveCount = $results.InconclusiveCount

            $obj.InvokeCompleteTime = Get-Date

            $results | Update-DbcPowerBiDataSource -Enviornment $groupName -Append -Path $PowerBiDataPath 
            $obj.WriteResultsTime = Get-Date       

            $obj.InvokeDuration = New-TimeSpan -Start $obj.InvokeStartTime -End $obj.InvokeCompleteTime
            $obj.ResultsDuration = New-TimeSpan -Start $obj.InvokeCompleteTime -End $obj.WriteResultsTime 

            $timeData += $obj        
        } # $tags | ForEach-Object               
} # $groups | ForEach-Object

$timeData |
    Select-Object ServerGroup, NumServers, tag, InvokeDuration,  TestExecution, ResultsDuration |
    ft -AutoSize

Send the timing analysis via email as an HTML table:

#https://gallery.technet.microsoft.com/scriptcenter/bd0c8d87-466f-4488-92a2-0f726cb6f4cd
. "$PSScriptRoots\Functions\Send-EmailHTML.ps1"

Send-EmailHTML -To $ToEmail -from $FromEmail -Subject "DBAChecks Has updated PowerBI" `
    -BodyAsArray ($timeData | 
        Select-Object ServerGroup, NumServers, tag, TotalCount, PassedCount, FailedCount, InvokeDuration,  TestExecution, ResultsDuration)