Auditing sysadmin on multiple servers using PowerShell

By:   |   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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley 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



Comments For This Article




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

 

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 7, 2012 - 5:12:12 AM - Domi Back To Top (19423)

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 5, 2012 - 10:49:23 AM - Domi Back To Top (19398)

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 (19169)

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 (19162)

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 (15728)

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 (15726)

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 (15725)

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















get free sql tips
agree to terms