Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Auditing sysadmin on multiple servers using PowerShell


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:





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





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, May 23, 2014 - 5:33:32 AM - Chhavi_MCITP Back To Top

 

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 Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

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


Learn more about SQL Server tools