I use this to retrieve all SQL sysadmins on a server, and drill down, if any are Active Directory groups, to the individuals.
$SQLInstance = "SQLDEV"; $DomainFilter = "MYCOMPANYDOMAIN" $Server = new-object Microsoft.SqlServer.Management.Smo.Server $SQLInstance; $SQLLogins = $Server.Logins; $sysadmins = $null; $sysadmins = foreach($sysadmin in $SQLLogins) { foreach($role in $sysadmin.ListMembers()) { if($role -match 'sysadmin') { Write-Verbose "sysadmin found: $($sysadmin.Name)" $sysadmin | Select-Object ` @{label = "SQLServer"; Expression = {$SQLInstance}}, ` @{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, ` Name, LoginType, CreateDate, DateLastModified; $sysadmin |?{$_.LoginType -eq 'WindowsGroup' -and $_.Name -match '$DomainFilter'} |%{ Get-ADGroupMembers -Group $_.Name.Replace("$DomainFilter\",'') |Sort-Object LastName | Select-Object ` @{label = "SQLServer"; Expression = {$SQLInstance}}, ` @{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, ` Name,@{label = "LoginType"; Expression = {'GroupUser'}} } }; }; }; $sysadmins | ft -AutoSize