Auditing sysadmin on multiple servers using PowerShell
By: K. Brian Kelley | 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.
View all my tips