POWERSHELL: Get all sysadmins on a SQL Server

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

Comments are closed.

Post Navigation