Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

RealSQLGuy - Helping You To Become A SQL Hero

My real name is Tracy McKibben. I’ve been working with database products for over 20 years, starting with FoxBase running on Xenix. Over the years, I’ve worked with all flavors of FoxPro, some Clipper and dBase, and starting somewhere around 1995, SQL Server. I’ve even dabbled with Oracle, though I’ve tried to block out all memories of that experience. At present, I’m the Senior SQL Server DBA and the DBA Team Supervisor for Pearson VUE. All opinions expressed on this site are my own and do not reflect the opinions of Pearson VUE.

Automated Permissions Auditing With Powershell and T-SQL: Part 3

rubbergloveshouse

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.

Comments

Leave a comment on the original post [realsqlguy.com, opens in a new window]

Loading comments...