PowerShell quick list of SQL Users with SysAdmin Role

Here’s a quick way to start getting a list of SQL Server users having “SysAdmin” Role.  Basically, I’m using SQLPS module (now available with SQL Server 2012) which loads all the SMO needed to help you script against your SQL engine.

This script does the following:

1. Import the SQLPS Module.
2. Connect to a SQL Server Instance.
3. Get the SQL Logins information.
4. Search for SQL users with “SysAdmin” Role, and builds a customized information in a PSObject.
5. Export the information to a CSV file.
6. Open the CSV file, which by default could open an Excel application(if installed on machine).

Here’s the code:

[sourcecode language=”powershell”]
Import-Module SQLPS -disablenamechecking

$SQLSvr = "SQLServername\Instancename";
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSvr;
$SQLLogins = $MySQL.Logins;

$SysAdmins = $null;
$SysAdmins = foreach($SQLUser in $SQLLogins)
{
foreach($role in $SQLUser.ListMembers())
{
if($role -match ‘sysadmin’)
{
Write-Host "SysAdmins found: $($SQLUser.Name)" -ForegroundColor Yellow;
$SQLUser | Select-Object `
@{label = "SQLServer"; Expression = {$SQLSvr}}, `
@{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, `
Name, LoginType, CreateDate, DateLastModified;
};
};
};

$SysAdmins | Export-Csv -Path ‘C:\temp\SQLSysAdminList.csv’ -Force -NoTypeInformation;
ii ‘C:\temp\SQLSysAdminList.csv’;
[/sourcecode]

Eventually, you could make changes to this scritp to be capable to access a list of SQL Servers and build your custom report.

Bonus:

To add the functionallity to connect to multiple servers, we can add a list of Servers and then using the “Foreach” statement to loop through the list, and with little changes to the previous code.

Here’s how it will look with just adding a few more line of code:

[sourcecode language=”powershell”]
## – Loads SQL Powerhell SMO and commands:
Import-Module SQLPS -disablenamechecking

## – BUild list of Servers manually (this builds an array list):
$SQLServers = "Server01","Server01\InstanceNameA","Server03";
$SysAdmins = $null;
foreach($SQLSvr in $SQLServers)
{

## – Add Code block:
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSvr;
$SQLLogins = $MySQL.Logins;

$SysAdmins += foreach($SQLUser in $SQLLogins)
{
foreach($role in $SQLUser.ListMembers())
{
if($role -match ‘sysadmin’)
{
Write-Host "SysAdmins found: $($SQLUser.Name)" -ForegroundColor Yellow;
$SQLUser | Select-Object `
@{label = "SQLServer"; Expression = {$SQLSvr}}, `
@{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, `
Name, LoginType, CreateDate, DateLastModified;
};
};
};
## – End of Code block

}

## – BUild and open report:
$SysAdmins | Export-Csv -Path ‘C:\temp\SQLSysAdminList.csv’ -Force -NoTypeInformation;
ii ‘C:\temp\SQLSysAdminList.csv’;
[/sourcecode]

That’s it!