Month: February 2025

Words: 593

Time to read: ~3 minutes

PowerShell Scripts for Checking SQL Server Login SIDs

Welcome back to T-SQL Tuesday, the monthly blogging party where we are given a topic and asked to write a blog post about it.

This month, we have Steve Jones [ blog ] asking us to talk about permissions.

Before

Normally, I would have skipped this month because I’ve talked about permissions before, and I feel like it’s one of the better posts I’ve written.
But I’m trying to write more, so that can’t be an excuse anymore.

So, let’s talk about AGs, DistAGs, and SQL Server authentication login SIDs.

AGs and DistAGs

In work, we have Availability Groups (AGs) and Distributed Availability Groups (DistAGs), depending on the environment level, e.g. Prod, Pre-Prod, QA, Dev, etc.

When we plan failovers for maintenance, we run through a checklist to ensure we can safely complete the failover.

One of these checks is to ensure that the correct logins with the correct SIDs are on each replica. Otherwise, we could get flooded with a lot of “HEY! My application doesn’t connect anymore! Why you break my application“, when apps try and log into the new primary and the user SIDs don’t match the login SIDs.

While I don’t have a problem with doing a query across registered servers, I threw together a quick script in PowerShell with dbatools that does the job of checking for me. And, like most things that happen in business, this temporary solution became part of our playbook.

Who knows! Maybe this quick, throwaway script could also become part of other people’s playbooks!

I’m not sure how I feel about that…

Scripts

We’re using dbatools for this because I think it’s the best tool for interacting with databases from a PowerShell session regardless of what Carbon Black complains about.

Getting the AG Replicas

Getting the AGs for each server is relatively simple, using a combination of Get-DbaRegServer and Get-DbaAvailabilityGroup.

Then, we can use the ever-respected Get-DbaLogin to get a list of logins and their SIDs per replica.
If we have a mismatch, we will have an issue after we failover. So best nip that in the bud now (also, I had this phrase SO wrong before I looked it up!).

$InstanceName = 'test' 
# $InstanceName = $null # Uncomment this line to get ALL
$Servers = Get-DbaRegServer

if ($InstanceName) {
	# Find the server that ends in our instance name
	$Servers = $Servers | Where-Object ServerName -match ('{0}$' -f $InstanceName)
}

# Get all replicas per Instance name that are part of AGs...
$DAGs = Get-DbaAvailabilityGroup -SqlInstance $Servers.ServerName |
	Where-Object AvailabilityGroup -notlike 'DAG*' | # Exclude DAGs which just show our underlying  AG names, per our naming conventions.
	ForEach-Object -Process {
		$instance = $null
		$instance = $_.InstanceName

		foreach ($r in $_.AvailabilityReplicas) { # I'd like 1 replica per line, please.
			[PSCustomObject] @{
				InstanceName = $instance
				Replica      = $r.Name
			}
		}
	} |
	Select-Object -Property InstanceName, Replica -Unique |
	Group-Object -Property InstanceName -AsHashTable

# Get a list of Logins/SIDs that don't match the count of replicas, i.e. someone forgot to create with SIDs...
foreach ($d in $DAGs.Keys) {
	Write-Verbose "Working on instance: $d" -Verbose
	Get-DbaLogin -SqlInstance $DAGs[$d].Replica |
		Group-Object -Property Name, Sid |
		Where-Object Count -ne $DAGs[$d].Count |
		Select-Object -Property @{ Name = 'Instance'; Expression = { $_.Group[0].InstanceName }},
			@{ Name = 'Occurances'; Expression = { $_.Count }},
			@{ Name = 'Login/SID'; Expression = { $_.Name }}
}

Cure

They say “prevention is better than cure”, and I’d love to get to a stage where we can “shift left” on these issues. Where we can catch them before someone creates a login with the same name but different SIDs on a replica.

But we’re not there yet. At least, we can find the issues just before they impact us.

That’s something, at least!