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 4

rubbergloveshouse

Previously, on REAL-SQL-Guy…

We were introduced to two SQL queries to obtain database login permissions, one for SQL Server 2000, one for SQL Server 2005 and higher.

We saw how these queries, combined with Powershell, can be used to loop through all databases on a SQL Server instance to collect permissions.  Cool stuff, but kind of useless unless we save that information someplace and actually make use of it.  Let’s expand the process a bit, to include writing the collected permissions data to a table.

First, we need to create a table, if one doesn’t already exist.  In order to use the script that I’m presenting here, the table MUST be placed on a SQL 2008 instance.

# Create AuditDBLogin table if it doesn't exist

$targetCmd.CommandText = "
IF OBJECT_ID('dbo.AuditDBLogin', 'U') IS NULL 
	CREATE TABLE dbo.AuditDBLogin (
		ServerName VARCHAR(50) NOT NULL, 
		Login VARCHAR(255) NOT NULL, 
		Username VARCHAR(255) NOT NULL, 
		FullName VARCHAR(255) NULL, 
		DatabaseName VARCHAR(255) NOT NULL, 
		SelectAccess BIT NOT NULL DEFAULT (0), 
		InsertAccess BIT NOT NULL DEFAULT (0), 
		UpdateAccess BIT NOT NULL DEFAULT (0), 
		DeleteAccess BIT NOT NULL DEFAULT (0), 
		DBOAccess BIT NOT NULL DEFAULT (0), 
		SysadminAccess BIT NOT NULL DEFAULT (0), 
		CONSTRAINT PK_AuditDBLogin PRIMARY KEY CLUSTERED 
			(ServerName, Login, Username, DatabaseName));"

$null = $targetCmd.ExecuteNonQuery()

Notice the column named FullName – remember this column, more on that later!

The reason for placing the collection table on a SQL 2008 instance is the use of the MERGE statement.  You can modify this to use the old INSERT/UPDATE logic if you’d like, but for sake of this example, we’re going assume the use of MERGE.

The evolving Powershell script now looks like this:

# 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
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$auditSMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist "$auditServerName"

# Specify server to audit
$targetServerName = "TARGETSERVER"
$targetDBName = "AuditDB"
$targetConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetServerName; Initial Catalog=$targetDBName; Integrated Security=SSPI")
$targetConn.Open()
$targetCmd = $targetConn.CreateCommand()

# Create AuditDBLogin table if it doesn't exist
$targetCmd.CommandText = "IF OBJECT_ID('dbo.AuditDBLogin', 'U') IS NULL CREATE TABLE dbo.AuditDBLogin (ServerName VARCHAR(50) NOT NULL, Login VARCHAR(255) NOT NULL, Username VARCHAR(255) NOT NULL, FullName VARCHAR(255) NULL, DatabaseName VARCHAR(255) NOT NULL, SelectAccess BIT NOT NULL DEFAULT (0), InsertAccess BIT NOT NULL DEFAULT (0), UpdateAccess BIT NOT NULL DEFAULT (0), DeleteAccess BIT NOT NULL DEFAULT (0), DBOAccess BIT NOT NULL DEFAULT (0), SysadminAccess BIT NOT NULL DEFAULT (0), CONSTRAINT PK_AuditDBLogin PRIMARY KEY CLUSTERED (ServerName, Login, Username, DatabaseName));"
$null = $targetCmd.ExecuteNonQuery()

# 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)
                            {
								$insertServerName = $row["ServerName"]
								$insertDatabaseName = $row["DatabaseName"]
								$insertLoginName = $row["LoginName"]
								$insertUserName = $row["LoginName"]
								$insertFullName = ""
								$insertSelect = $row["SelectAccess"]
								$insertInsert = $row["InsertAccess"]
								$insertUpdate = $row["UpdateAccess"]
								$insertDelete = $row["DeleteAccess"]
								$insertDBO = $row["DBOAccess"]
								$insertSysadmin = $row["SysadminAccess"]
								$targetCmd.CommandText = "
MERGE dbo.AuditDBLogin AS tgt
USING
	(
		SELECT
			ServerName				= '$insertServerName',
			Login					= '$insertLoginName',
			Username				= '$insertUsername',
			FullName				= '$insertFullName',
			DatabaseName			= '$insertDatabaseName',
			SelectAccess			= $insertSelect,
			InsertAccess			= $insertInsert,
			UpdateAccess			= $insertUpdate,
			DeleteAccess			= $insertDelete,
			DBOAccess				= $insertDBO,
			SysadminAccess			= $insertSysadmin
	) AS src (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)
		ON 
			(
				tgt.ServerName = src.ServerName
				AND tgt.Login = src.Login
				AND tgt.Username = src.Username
				AND tgt.FullName = src.FullName
				AND tgt.DatabaseName = src.DatabaseName
			)
	WHEN NOT MATCHED THEN
		INSERT (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)
			VALUES (src.ServerName, src.Login, src.Username, src.FullName, src.DatabaseName, src.SelectAccess, src.InsertAccess, src.UpdateAccess, src.DeleteAccess, src.DBOAccess, src.SysadminAccess)
	WHEN MATCHED THEN
		UPDATE
			SET
				SelectAccess		= CASE WHEN src.SelectAccess > tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,
				InsertAccess		= CASE WHEN src.InsertAccess > tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,
				UpdateAccess		= CASE WHEN src.UpdateAccess > tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,
				DeleteAccess		= CASE WHEN src.DeleteAccess > tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,
				DBOAccess			= CASE WHEN src.DBOAccess > tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,
				SysadminAccess		= CASE WHEN src.SysadminAccess > tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;
														"
								$null = $targetCmd.ExecuteNonQuery()
                            }
                    }
            }
    }

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

Comments

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

Loading comments...