Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Auditing sysadmin on multiple servers using PowerShell

MSSQLTips author K. Brian Kelley By:   |   Read Comments (8)   |   Related Tips: More > 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


Last Update: 1/23/2012


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, May 23, 2014 - 5:33:32 AM - Chhavi_MCITP Read The Tip

 

Thanks for this. Really got the idea but when tried to implement , getting below error:

 

I can connect to SQL Server and get the output of below command by manually running it on SSMS.

 

EXEC

sp_helpsrvrolemember'sysadmin'

--------------------------------------------------------

 

 

Exception calling "Fill" with "1" argument(s): "The ConnectionString property has not been initialized."

+ [void] $sqlAdapter.Fill($sqlDataSet);

 

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException

 

+ FullyQualifiedErrorId : InvalidOperationException

 

 

 

 

 


Friday, September 07, 2012 - 5:12:12 AM - Domi Read The Tip

Something was wrong with the connection. Rewrote it to.

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
 $sqlConn.ConnectionString = "Data Source=$SQLServer;Integrated Security=TRUE; Initial Catalog=mydb"

Everything works fine know.

thnx for the script it helps a lot


Wednesday, September 05, 2012 - 10:49:23 AM - Domi Read The Tip

Thnx 4 this. But i get the following error:

Exception calling "Fill" with "1" argument(s): "The ConnectionString property has not been initialized."

At c:\myfolder\test.ps1:28 char:24

+     [void]$sqlAdapter.Fill( <<<< $sqlDataSet);

Unable to index into an object of type System.Data.DataTabelCollection

At c:\myfolder\test.ps1:31 char:38

+     ForEach ($row in $sqlDataSet.Tables[0 <<< ])

 

I tried only the connection part. It works but something goes wrong in the last part

Any tips ???????


 $sqlQuery = "EXEC sp_helpsrvrolemember 'sysadmin';";
 $sqlCmd = New-Object System.Data.SqlClient.SqlCommand($sqlQuery, $sqlConn);
 $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
 $sqlAdapter.SelectCommand = $sqlCmd;
 $sqlDataset = New-Object System.Data.DataSet;
 [void]$sqlAdapter.Fill($sqlDataSet); 

 ForEach ($row in $sqlDataSet.Tables[0])
 {   
 $row["MemberName"] | Out-File -filepath $writeFile -append;
 } 


Wednesday, August 22, 2012 - 9:35:25 AM - K. Brian Kelley Read The Tip

ESL,

  it looks like the SqlCommand isn't being initialized correctly. Are you sure you have a valid connection?

 


Wednesday, August 22, 2012 - 8:44:10 AM - ESL Read The Tip

hI?

I GOT THIS ERROR WHEN I TRY TO LAUNCH THE CODE

Exception calling "Fill" with "1" argument(s): "The SelectCommand property has not been initialized before calling 'Fill'."
At C:\powersql\DATABASE\Recherche_Sysadmin_rights.ps1:19 char:18
+  $sqlAdapter.Fill <<<< ($sqlDataSet);
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Cannot index into a null array.
At C:\powersql\DATABASE\Recherche_Sysadmin_rights.ps1:24 char:7
+  $row[ <<<< "MemberName"] | Out-File -filepath $writeFile -append;
    + CategoryInfo          : InvalidOperation: (MemberName:String) [], RuntimeException
    + FullyQualifiedErrorId : NullArray


Monday, January 23, 2012 - 10:00:37 AM - K. Brian Kelley Read The Tip

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.

 


Monday, January 23, 2012 - 7:58:13 AM - Jason Read The Tip

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 - 7:06:09 AM - BJ Read The Tip

Great info.  We are going to be looking into this soon and run it across our servers.




 
Sponsor Information