Who’s in those AD Windows Groups setup on SQL Server?

I wanted to be able to check which windows users had been placed in the Windows AD Groups we use to control access to SQL Server. Here’s what I came up with to make checking this easy;

?View Code POWERSHELL
Import-Module SQLPS -DisableNameChecking -ErrorAction Ignore;
Import-Module ActiveDirectory -DisableNameChecking -ErrorAction Ignore;
 
$sql_server = "sql_instance";
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $sql_server;
 
$groups = $srv.Logins | Where-Object {$_.LoginType -eq "WindowsGroup";};
 
foreach($group in $groups)
{
	# Can't find an appropriate property with just the name, anyone know?
	$name = $group.Name;
	# Extract name
	$name = $name.SubString($name.IndexOf("`\") + 1);
	if($name -ne "MSSQLSERVER" -and $name -ne "SQLSERVERAGENT")
	{
		$name;
		Write-Host "==============================";
		Get-ADGroupMember -Identity $name | Select-Object Name;
		Write-Host "==============================`n";
	}
}

Output will look something like below;

Group1
==============================
User 1
User 2
User 3
User 4
==============================

Group2
==============================
User 1
User 2
User 3
==============================

Leave a Reply