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;
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
==============================