Auditing sysadmin on multiple servers using PowerShell

By: K. Brian Kelley   |   Updated: 2012-01-23   |   Comments (8)   |   Related: > Auditing and Compliance

Problem

I have a requirement to periodically audit for members of the sysadmin role across all my servers. I'd like to automate this and I've heard PowerShell is a good match. In order to feed a downstream governance application, I'd like to simply output the members into text files. How can I do this?

Solution

You're correct: PowerShell is a good match for this.

First, we need to get a list of the SQL Server instances into PowerShell to cycle through. A good way to do this is a simple text file with an instance per line. In this example I manually created a file c:\scripts\servers.txt and entered each SQL Server instance I wanted to check. 

Here's how to open up that text file in PowerShell and begin to loop through for each instance:

# $filename contains the list of servers to audit
$filename = "c:\scripts\servers.txt";

# cycle through each SQL Server instance and report the findings to a text file
ForEach ($SQLServer in Get-Content $filename)
{
}

With that done, the next thing we'll have to do is make a connection to SQL Server and issue the appropriate query to return the members of the sysadmin role. There are a few objects we can use. They are:

• a SQL Server connection using System.Data.SqlClient.SqlConnection
• A command to execute using System.Data.SqlClient.SqlCommand
• An adapter to process the results using System.Data.SqlClient.SqlDataAdapter
• A dataset to hold the results using System.Data.DataSet

Here's how this modifies our script:

# $filename contains the list of servers to audit
$filename = "c:\scripts\servers.txt";

# cycle through each SQL Server instance and report the findings to a text file
ForEach ($SQLServer in Get-Content $filename)
{
 # The ` (below the tilde on most keyboards) tells PowerShell the command continues
 # on the next line
 $sqlConn = New-Object System.Data.SqlClient.SqlConnection `
 ("Data Source=$SQLServer;Integrated Security=TRUE;Initial Catalog=master");
 $sqlConn.Open;
 
 # Having opened the connection to the SQL Server instance, 
 # now we'll use sp_helpsrvrolemember to query for sysadmin members.
 # This works across supported SQL Server versions (from 2000 - 2012).
 $sqlQuery = "EXEC sp_helpsrvrolemember 'sysadmin';";
 $sqlCmd = New-Object System.Data.SqlClient.SqlCommand($sqlQuery, $sqlConn);

 $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlCmd;
 $sqlDataset = New-Object System.Data.DataSet;
 [void] $sqlAdapter.Fill($sqlDataSet);
 
 # Output each member to the text file for the instance
 ForEach ($row in $sqlDataSet.Tables[0])
 {
 }
 
 $sqlConn.Close;

}

I've chosen to use the system stored procedure sp_helpsrvrolemember because I know it's supported across all version of SQL Server we're likely to be auditing. That keeps things simple and means I don't have to do any version checking, which would complicate the script. If I was looking for more information than this, like if I was looking for explicit permissions at the server level (possible with SQL Server 2005 and above but not with SQL Server 2000), then I'd have to know the version so I wouldn't issue an invalid command.

We'll also want to output each row from our query into the text file. For this we'll need a directory to hold all of our audit files, one per instance. We'll also need to utilize the Out-File cmdlet to get the information into those text files. Do note that we use the -append switch with Out-File. If you don't use this, every time a new row is written, the file will be overwritten. We don't want that. Using the -append switch will also not cause any problems if a file doesn't already exist. PowerShell will simply create the file for us.

 That makes our final script look like so:

# $filename contains the list of servers to audit
# $reportsDir is the directory to write the audit results
$filename = "c:\scripts\servers.txt";
$reportsDir = "c:\scripts\reports\";

# cycle through each SQL Server instance and report the findings to a text file
ForEach ($SQLServer in Get-Content $filename)
{
 # if an instance is specified, replace the "\" which would mean a directory 
 # when writing the file
 $writeFile = $reportsDir + $SQLServer.Replace("\", "_") + "_sysadmins.txt";

 # The ` (below the tilde on most keyboards) tells PowerShell the command continues
 # on the next line
 $sqlConn = New-Object System.Data.SqlClient.SqlConnection `
 ("Data Source=$SQLServer;Integrated Security=TRUE;Initial Catalog=master");
 $sqlConn.Open;
 
 # Having opened the connection to the SQL Server instance, 
 # now we'll use sp_helpsrvrolemember to query for sysadmin members.
 # This works across supported SQL Server versions (from 2000 - 2012).
 $sqlQuery = "EXEC sp_helpsrvrolemember 'sysadmin';";
 $sqlCmd = New-Object System.Data.SqlClient.SqlCommand($sqlQuery, $sqlConn);

 $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlCmd;
 $sqlDataset = New-Object System.Data.DataSet;
 [void] $sqlAdapter.Fill($sqlDataSet);
 
 # Output each member to the text file for the instance
 ForEach ($row in $sqlDataSet.Tables[0])
 {
    $row["MemberName"] | Out-File -filepath $writeFile -append;
 }
 
 $sqlConn.Close;

}

At this point you just need to populate the servers.txt file with your SQL Server instance names and then run the PowerShell script.

Next Steps

• See additional tips on using PowerShell to return information on SQL Server.
• See how to audit when a member of the sysadmin role logs on to a SQL Server (2005 and above).
• Learn what each server level role does in SQL Server.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips