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
