In this episode, I’m going to start introducing some of the Powershell elements that tie this audit process together.

DISCLAIMER:  I am NOT a Powershell expert, I have only recently begun to use it.  There are likely parts of this process that can be done differently, more efficiently, or are just plain wrong.  If there are Powershell gurus in the house, please comment, let’s make this a two-way educational process.

Whew, that’s out of the way, so let’s move on.

In parts 1 and 2 of this series, I showed you queries for SQL Server 2005/2008 and SQL Server 2000, that will dump a summary of user permissions from the current database.  In this, part 3, I will show you the beginnings of a Powershell script that will utilize those two queries.

The script below, when run against a SQL Server instance, will loop through each database on the instance, and will “dump” the user permissions for each database.  Doesn’t seem like much, yet, but in the very near future, I’ll show you how to actually make use of the results of this script.  Baby steps…

# Specify server to audit
# Note the non-standard port number of 50000.  This can be omitted if SQL Server is listening on standard port 1433
$auditServerName = "REALSQLGUYSERVER,50000"
# Create connection object to SQL Server instance
# Note the non-standard port number of 50000.  This can be omitted if SQL Server is listening on standard port 1433
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$auditSMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist "$auditServerName"
# SQL Server is 2000
if ($auditSMOServer.Information.VersionMajor -eq 8)
{
$UserDumpQueryText = "query from part 2"
}
# SQL Server is 2005 or greater
elseif ($auditSMOServer.Information.VersionMajor -gt 8)
{
$UserDumpQueryText = "query from part 1"
}
# Unknown or unexpected SQL Server version
else
{
$UserDumpQueryText = "Unsupported SQL Server version"
}
# Version is good, let's proceed
if ($UserDumpQueryText -ne "Unsupported SQL Server version")
{
# Loop through each database on the SQL instance
foreach ($dbAudit in $auditSMOserver.databases)
{
if ($dbAudit.IsAccessible)
{
# Connect to the database
$auditDBName = $dbAudit.Name
$auditConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$auditServerName; Initial Catalog=$auditDBName; Integrated Security=SSPI")
$auditConn.Open()
$auditCmd = $auditConn.CreateCommand()
# Run the permissions dump query against the current database
$auditCmd.CommandText = $UserDumpQueryText
$queryresults = $auditCmd.ExecuteReader()
# Loop through permission dump resultset, print each value
foreach ($row in $queryresults)
{
echo $row["ServerName"]
echo $row["DatabaseName"]
echo $row["LoginName"]
echo $row["SelectAccess"]
echo $row["InsertAccess"]
echo $row["UpdateAccess"]
echo $row["DeleteAccess"]
echo $row["DBOAccess"]
echo $row["SysadminAccess"]
}
}
}
}

The post Automated Permissions Auditing With Powershell and T-SQL: Part 3 appeared first on RealSQLGuy.