Auditing sysadmin on multiple servers using PowerShell

By: K. Brian Kelley | Read Comments (3) | Print Brian is a SQL Server author and columnist focusing primarily on SQL Server security. Related Tips: More

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.

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Monday, January 23, 2012 - 7:06:09 AM - BJ
Great info.  We are going to be looking into this soon and run it across our servers.

Monday, January 23, 2012 - 7:58:13 AM - Jason
You audit only for members of the sysadmin role, and not the securityadmin role?  Isn't that like closing only half the windows in a house during a storm?

Monday, January 23, 2012 - 10:00:37 AM - K. Brian Kelley
Hi Jason,   it's not that auditing for securityadmin isn't important, it is, but so are most of the server-lever roles. However, the purpose of this article is to show how to do this sort of auditing using PowerShell, not be comprehensive on what to audit. Hope that makes sense.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code