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 6

rubbergloveshouse

Everybody still with me?  Have you tried the script from Part 5?  Did you notice how SLOW the Active Directory lookups are?

They’re not fast, that’s for sure. On one of my servers, the number of databases is somewhere in the neighborhood of 120, the old “one database per customer” model.  The permissions across these databases are nearly the same, which means that when I run this script, it’s repeating the same Active Directory lookups over and over again.  If there was ever a case for caching, this is it.  See where I’m going with this?

Let’s add caching to our script, to cut down on those redundant lookups.  First, we need someplace to store the cached data.  Someplace like, oh, a database table?

# Create AuditDBUserCache table if it doesn't exist
$targetCmd.CommandText = "IF OBJECT_ID('dbo.AuditDBUserCache', 'U') IS NULL CREATE TABLE dbo.AuditDBUserCache (UserName VARCHAR(255) NOT NULL PRIMARY KEY CLUSTERED, FullName VARCHAR(255));"
$null = $targetCmd.ExecuteNonQuery()

# Create AuditDBGroupCache table if it doesn't exist
$targetCmd.CommandText = "IF OBJECT_ID('dbo.AuditDBGroupCache', 'U') IS NULL CREATE TABLE dbo.AuditDBGroupCache (Login VARCHAR(255) NOT NULL, UserName VARCHAR(255) NOT NULL, CONSTRAINT PK_AuditDBGroupCache PRIMARY KEY CLUSTERED (Login, Username));"
$null = $targetCmd.ExecuteNonQuery()

# Empty cache tables for reloading
$targetCmd.CommandText = "TRUNCATE TABLE dbo.AuditDBUserCache; TRUNCATE TABLE dbo.AuditDBGroupCache;"
$null = $targetCmd.ExecuteNonQuery()

Next, we need to modify the script to make use of these cache tables, by adding lines of code similar to this to our script:

# Cache individual user
$targetCmd.CommandText = "SELECT CacheCount = COUNT(*) FROM dbo.AuditDBUserCache WHERE UserName = '$insertLoginName'"
$queryresults = $targetCmd.ExecuteReader()
$queryresults.Read()
$userCached = $queryresults["CacheCount"]
$queryresults.Close()
$queryresults.Dispose()

# Cache group
$targetCmd.CommandText = "SELECT CacheCount = COUNT(*) FROM dbo.AuditDBGroupCache WHERE Login = '$insertLoginName'"
$queryresults = $targetCmd.ExecuteReader()
$queryresults.Read()
$loginCached = $queryresults["CacheCount"]
$queryresults.Close()
$queryresults.Dispose()

These changes reduce the expensive Active Directory work, moving it into SQL Server which, as we know, does a pretty good job at storing and retrieving data.

So….

We now have a (fast) script that will dump all of our database permissions into a table.  Seems like we should do something to take advantage of that collected data, don’t you think?  Might I suggest that we build an email alerting mechanism, that will notify us when permissions changes are detected?  C’mon, it’s not hard to do!

First, we need to create another table:

# Create AuditDBLoginPrevious table if it doesn't exist
$targetCmd.CommandText = "IF OBJECT_ID('dbo.AuditDBLoginPrevious', 'U') IS NULL CREATE TABLE dbo.AuditDBLoginPrevious (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_AuditDBLoginPrevious PRIMARY KEY CLUSTERED (ServerName, Login, Username, DatabaseName));"
$null = $targetCmd.ExecuteNonQuery()

Each time the script is run, this table is purged, and the existing data from the last execution is moved here:

# Archive rows in AuditDBLogin for this server
$targetCmd.CommandText = "DELETE FROM dbo.AuditDBLoginPrevious WHERE ServerName = '$auditServerParam'; INSERT INTO dbo.AuditDBLoginPrevious (ServerName, Login, UserName, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess) SELECT ServerName, Login, UserName, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess FROM dbo.AuditDBLogin WHERE ServerName = '$auditServerParam'; DELETE FROM dbo.AuditDBLogin WHERE ServerName = '$auditServerParam';"
$null = $targetCmd.ExecuteNonQuery()

Second, we need a query that will compare the “old” data to the “new” data, and tell us what has changed:

$reportConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetServerName; Initial Catalog=$targetDBName; Integrated Security=SSPI")
$reportConn.Open()
$reportCmd = $reportConn.CreateCommand()

$reportCmd.CommandText = "
SELECT ServerName, UserName, DatabaseName, DescriptionOfChange
FROM
	(
		SELECT
			AccessLevel = ROW_NUMBER() OVER(PARTITION BY ServerName, UserName ORDER BY AccessLevel),
			ServerName, UserName, DatabaseName, DescriptionOfChange
		FROM
			(
				/* New server logins */
				SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'new' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				EXCEPT
				SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'new' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				UNION
				/* Revoked server logins */
				SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				EXCEPT
				SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				UNION
				/* New read-only access */
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read granted' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE SelectAccess = 1
					AND COALESCE(DatabaseName, '') <> ''
				EXCEPT
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read granted' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE SelectAccess = 1
					AND COALESCE(DatabaseName, '') <> ''
				UNION
				/* Revoked read-only access */
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE SelectAccess = 1
					AND COALESCE(DatabaseName, '') <> ''
				EXCEPT
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE SelectAccess = 1
					AND COALESCE(DatabaseName, '') <> ''
				UNION
				/* New write access */
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write granted' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1
					AND COALESCE(DatabaseName, '') <> ''
				EXCEPT
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write granted' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1
					AND COALESCE(DatabaseName, '') <> ''
				UNION
				/* Revoked write access */
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1
					AND COALESCE(DatabaseName, '') <> ''
				EXCEPT
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1
					AND COALESCE(DatabaseName, '') <> ''
				UNION
				/* New DBO access */
				SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO granted' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE DBOAccess = 1
					AND COALESCE(DatabaseName, '') <> ''
				EXCEPT
				SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO granted' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE DBOAccess = 1
					AND COALESCE(DatabaseName, '') <> ''
				UNION
				/* Revoked DBO access */
				SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE DBOAccess = 1
					AND COALESCE(DatabaseName, '') <> ''
				EXCEPT
				SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE DBOAccess = 1
					AND COALESCE(DatabaseName, '') <> ''
				UNION
				/* New sysadmin access */
				SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN granted' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE SysadminAccess = 1
				EXCEPT
				SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN granted' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE SysadminAccess = 1
				UNION
				/* Revoked sysadmin access */
				SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE SysadminAccess = 1
				EXCEPT
				SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE SysadminAccess = 1
			) AS AllChanges
	) AS RankedChanges
WHERE AccessLevel = 1
	AND UserName <> ''
"

$queryresults = $reportCmd.ExecuteReader()

Last, but not least, we need to funnel those changes into an email message, maybe with color coding to call out the really important stuff:

$emailbody = ""

foreach ($row in $queryresults)
	{
		$serverName = $row["ServerName"]
		$userName = $row["UserName"]
		$databaseName = $row["DatabaseName"]
		$descriptionOfChange = $row["DescriptionOfChange"]

		if ($descriptionOfChange -eq "new") 
			{
				$emailbody = $emailbody + '<b>New login ' + $userName + ' created on server ' + $serverName + '</b>'
			}
		elseif ($descriptionOfChange -eq "revoked") 
			{
				$emailbody = $emailbody + '<b>Login ' + $userName + ' dropped from server ' + $serverName + '</b>'
			}
		elseif ($descriptionOfChange -eq "SYSADMIN granted") 
			{
				$emailbody = $emailbody + '' + $userName + ' granted SYSADMIN rights to server ' + $serverName + ''
			}
		elseif ($descriptionOfChange -eq "SYSADMIN revoked") 
			{
				$emailbody = $emailbody + '' + $userName + ' SYSADMIN rights to server ' + $serverName + ' revoked'
			}
		elseif ($descriptionOfChange -eq "DBO granted") 
			{
				$emailbody = $emailbody + '' + $userName + ' granted DBO access to database ' + $databaseName + ' on server ' + $serverName + ''
			}
		elseif ($descriptionOfChange -eq "DBO revoked") 
			{
				$emailbody = $emailbody + '' + $userName + ' DBO access to database ' + $databaseName + ' revoked on server ' + $serverName + ''
			}
		elseif ($descriptionOfChange -eq "write granted") 
			{
				$emailbody = $emailbody + '' + $userName + ' granted WRITE access to database ' + $databaseName + ' on server ' + $serverName + ''
			}
		elseif ($descriptionOfChange -eq "write revoked") 
			{
				$emailbody = $emailbody + '' + $userName + ' WRITE access to database ' + $databaseName + ' revoked on server ' + $serverName + ''
			}
		elseif ($descriptionOfChange -eq "read revoked")
			{
				$emailbody = $emailbody + '' + $userName + ' READ access to database ' + $databaseName + ' revoked on server ' + $serverName + ''
			}
		else 
			{
				$emailbody = $emailbody + '' + $userName + ' granted READ access to database ' + $databaseName + ' on server ' + $serverName + ''
			}
	}

if ($emailbody)
	{
		$emailbody = 'The following changes to database access were detected:

' + $emailbody
		$emailbody += ''

		#Send email
		$SMTPserver = "real.smtp.server"
		$from = "real@real-sql-guy.com"
		$to = "real@real-sql-guy.com"
		$subject = "Database Access Changes Detected"

		$mailer = new-object Net.Mail.SMTPclient($SMTPserver)
		$msg = new-object Net.Mail.MailMessage($from, $to, $subject, $emailbody)
		$msg.IsBodyHTML = $true
		$mailer.send($msg)

		$msg.Dispose()
	}

Pretty easy, huh?  Set this up to run on a schedule, and you have an automated babysitter keeping an eye out for unauthorized permissions changes.  There’s one more feature that I want to add to this, but I’ll save that for the next episode.

Here’s the full script, so that you don’t have to try inserting these changes yourself.  Notice also that the script now accepts parameters – the target server, target database, and a comma-delimited list of servers to audit:

param([string]$auditServerList, [string]$targetServer, [string]$targetDBName)

Add-PSSnapin Quest.ActiveRoles.ADManagement

if (!($auditServerList))
	{
		echo "No audit server(s) specified"
		break
	}

if (!($targetServer))
	{
		echo "No target server specified"
		break
	}

if (!($targetDBName))
	{
		echo "No target database specified"
		break
	}

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$targetSMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist "$targetServer"
$dbTarget = $targetSMOserver.databases[$targetDBName]

$targetConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetServer; Initial Catalog=$targetDBName; Integrated Security=SSPI")
$targetConn.Open()
$targetCmd = $targetConn.CreateCommand()

$Now = Get-Date

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

# Create AuditDBLoginPrevious table if it doesn't exist
$targetCmd.CommandText = "IF OBJECT_ID('dbo.AuditDBLoginPrevious', 'U') IS NULL CREATE TABLE dbo.AuditDBLoginPrevious (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_AuditDBLoginPrevious PRIMARY KEY CLUSTERED (ServerName, Login, Username, DatabaseName));"
$null = $targetCmd.ExecuteNonQuery()

# Create AuditDBUserCache table if it doesn't exist
$targetCmd.CommandText = "IF OBJECT_ID('dbo.AuditDBUserCache', 'U') IS NULL CREATE TABLE dbo.AuditDBUserCache (UserName VARCHAR(255) NOT NULL PRIMARY KEY CLUSTERED, FullName VARCHAR(255));"
$null = $targetCmd.ExecuteNonQuery()

# Create AuditDBGroupCache table if it doesn't exist
$targetCmd.CommandText = "IF OBJECT_ID('dbo.AuditDBGroupCache', 'U') IS NULL CREATE TABLE dbo.AuditDBGroupCache (Login VARCHAR(255) NOT NULL, UserName VARCHAR(255) NOT NULL, CONSTRAINT PK_AuditDBGroupCache PRIMARY KEY CLUSTERED (Login, Username));"
$null = $targetCmd.ExecuteNonQuery()

# Empty cache tables for reloading
$targetCmd.CommandText = "TRUNCATE TABLE dbo.AuditDBUserCache; TRUNCATE TABLE dbo.AuditDBGroupCache;"
$null = $targetCmd.ExecuteNonQuery()

foreach ($auditServerParam in $auditServerList.Split())
	{
		# Archive rows in AuditDBLogin for this server
		$targetCmd.CommandText = "DELETE FROM dbo.AuditDBLoginPrevious WHERE ServerName = '$auditServerParam'; INSERT INTO dbo.AuditDBLoginPrevious (ServerName, Login, UserName, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess) SELECT ServerName, Login, UserName, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess FROM dbo.AuditDBLogin WHERE ServerName = '$auditServerParam'; DELETE FROM dbo.AuditDBLogin WHERE ServerName = '$auditServerParam';"
		$null = $targetCmd.ExecuteNonQuery()

		$memberList = @()

		$auditSMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist "$auditServerParam"

		if ($auditSMOServer.Information.VersionMajor -eq 8)
			{
				$UserDumpQueryText = "
		SELECT
			ServerName				= @@SERVERNAME,
			LoginName				= AccessSummary.LoginName,
			LoginType				= CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,
			DatabaseName			= DB_NAME(),
			SelectAccess			= MAX(AccessSummary.SelectAccess),
			InsertAccess			= MAX(AccessSummary.InsertAccess),
			UpdateAccess			= MAX(AccessSummary.UpdateAccess),
			DeleteAccess			= MAX(AccessSummary.DeleteAccess),
			DBOAccess				= MAX(AccessSummary.DBOAccess),
			SysadminAccess			= MAX(AccessSummary.SysadminAccess)
		FROM
			(
				/* Get logins with permissions */
				SELECT 
					LoginName				= sysusers.name,
					SelectAccess			= CASE WHEN sysprotects.action = 193 THEN 1 ELSE 0 END,
					InsertAccess			= CASE WHEN sysprotects.action = 195 THEN 1 ELSE 0 END,
					UpdateAccess			= CASE WHEN sysprotects.action = 196 THEN 1 ELSE 0 END,
					DeleteAccess			= CASE WHEN sysprotects.action = 197 THEN 1 ELSE 0 END,
					DBOAccess				= 0,
					SysadminAccess			= 0
				FROM dbo.sysobjects
				INNER JOIN dbo.sysprotects
					ON sysprotects.id = sysobjects.id
				INNER JOIN dbo.sysusers
					ON sysusers.uid = sysprotects.uid
				INNER JOIN master.dbo.syslogins AS syslogins
					ON syslogins.sid = sysusers.sid
				WHERE sysobjects.type IN ('U', 'V')
					AND sysusers.issqlrole = 0
					AND sysprotects.protecttype IN (204, 205)
					AND sysprotects.action IN (193, 195, 196, 197)
				UNION ALL
				/* Get group members with permissions */
				SELECT 
					LoginName				= sysusersMember.name,
					SelectAccess			= CASE WHEN sysprotects.action = 193 THEN 1 ELSE 0 END,
					InsertAccess			= CASE WHEN sysprotects.action = 195 THEN 1 ELSE 0 END,
					UpdateAccess			= CASE WHEN sysprotects.action = 196 THEN 1 ELSE 0 END,
					DeleteAccess			= CASE WHEN sysprotects.action = 197 THEN 1 ELSE 0 END,
					DBOAccess				= 0,
					SysadminAccess			= 0
				FROM dbo.sysobjects
				INNER JOIN dbo.sysprotects
					ON sysprotects.id = sysobjects.id
				INNER JOIN dbo.sysusers AS sysusersRole
					ON sysusersRole.uid = sysprotects.uid
				INNER JOIN dbo.sysmembers
					ON sysmembers.groupuid = sysusersRole.uid
				INNER JOIN dbo.sysusers AS sysusersMember
					ON sysusersMember.uid = sysmembers.memberuid
				INNER JOIN master.dbo.syslogins AS syslogins
					ON syslogins.sid = sysusersMember.sid
				WHERE sysobjects.type IN ('U', 'V')
					AND sysusersRole.issqlrole = 1
					AND sysusersRole.name NOT IN ('public')
					AND sysprotects.protecttype IN (204, 205)
					AND sysprotects.action IN (193, 195, 196, 197)
				UNION ALL
				/* Get users in db_owner, db_datareader and db_datawriter */
				SELECT
					LoginName				= syslogins.name,
					SelectAccess			= CASE WHEN sysusers.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,
					InsertAccess			= CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
					UpdateAccess			= CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
					DeleteAccess			= CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
					DBOAccess				= CASE WHEN sysusers.name IN ('db_owner') THEN 1 ELSE 0 END,
					SysadminAccess			= 0
				FROM dbo.sysusers
				INNER JOIN dbo.sysmembers
					ON sysmembers.groupuid = sysusers.uid
				INNER JOIN dbo.sysusers AS sysusersMember
					ON sysusersMember.uid = sysmembers.memberuid
				INNER JOIN master.dbo.syslogins AS syslogins
					ON syslogins.sid = sysusersMember.sid
				WHERE sysusers.name IN ('db_owner', 'db_datareader', 'db_datawriter')
				UNION ALL
				/* Get users in sysadmin */
				SELECT
					LoginName				= syslogins.name,
					SelectAccess			= 1,
					InsertAccess			= 1,
					UpdateAccess			= 1,
					DeleteAccess			= 1,
					DBOAccess				= 0,
					SysadminAccess			= 1
				FROM master.dbo.syslogins AS syslogins
				WHERE syslogins.sysadmin = 1
			) AS AccessSummary
		INNER JOIN master.dbo.syslogins AS syslogins
			ON syslogins.loginname = AccessSummary.LoginName
		WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT')
		GROUP BY
			AccessSummary.LoginName,
			CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END
				"
			}
		elseif ($auditSMOServer.Information.VersionMajor -gt 8)
			{
				$UserDumpQueryText = "
		SELECT
			ServerName				= @@SERVERNAME,
			LoginName				= AccessSummary.LoginName,
			LoginType				= CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,
			DatabaseName			= DB_NAME(),
			SelectAccess			= MAX(AccessSummary.SelectAccess),
			InsertAccess			= MAX(AccessSummary.InsertAccess),
			UpdateAccess			= MAX(AccessSummary.UpdateAccess),
			DeleteAccess			= MAX(AccessSummary.DeleteAccess),
			DBOAccess				= MAX(AccessSummary.DBOAccess),
			SysadminAccess			= MAX(AccessSummary.SysadminAccess)
		FROM
			(
				/* Get logins with permissions */
				SELECT 
					LoginName				= sysDatabasePrincipal.name,
					SelectAccess			= CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,
					InsertAccess			= CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,
					UpdateAccess			= CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,
					DeleteAccess			= CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,
					DBOAccess				= 0,
					SysadminAccess			= 0
				FROM sys.database_permissions AS sysDatabasePermission
				INNER JOIN sys.database_principals AS sysDatabasePrincipal
					ON sysDatabasePrincipal.principal_id = sysDatabasePermission.grantee_principal_id
				INNER JOIN sys.server_principals AS sysServerPrincipal
					ON sysServerPrincipal.sid = sysDatabasePrincipal.sid
				WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'
					AND sysDatabasePrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')
					AND sysServerPrincipal.is_disabled = 0
				UNION ALL
				/* Get group members with permissions */
				SELECT 
					LoginName				= sysDatabasePrincipalMember.name,
					SelectAccess			= CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,
					InsertAccess			= CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,
					UpdateAccess			= CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,
					DeleteAccess			= CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,
					DBOAccess				= 0,
					SysadminAccess			= 0
				FROM sys.database_permissions AS sysDatabasePermission
				INNER JOIN sys.database_principals AS sysDatabasePrincipalRole
					ON sysDatabasePrincipalRole.principal_id = sysDatabasePermission.grantee_principal_id
				INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
					ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
				INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
					ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
				INNER JOIN sys.server_principals AS sysServerPrincipal
					ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
				WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'
					AND sysDatabasePrincipalRole.type_desc = 'DATABASE_ROLE'
					AND sysDatabasePrincipalRole.name &lt;&gt; 'public'
					AND sysDatabasePrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')
					AND sysServerPrincipal.is_disabled = 0
				UNION ALL
				/* Get users in db_owner, db_datareader and db_datawriter */
				SELECT
					LoginName				= sysServerPrincipal.name,
					SelectAccess			= CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,
					InsertAccess			= CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
					UpdateAccess			= CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
					DeleteAccess			= CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
					DBOAccess				= CASE WHEN sysDatabasePrincipalRole.name = 'db_owner' THEN 1 ELSE 0 END,
					SysadminAccess			= 0
				FROM sys.database_principals AS sysDatabasePrincipalRole
				INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
					ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
				INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
					ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
				INNER JOIN sys.server_principals AS sysServerPrincipal
					ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
				WHERE sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader', 'db_datawriter')
					AND sysServerPrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')
					AND sysServerPrincipal.is_disabled = 0
				UNION ALL
				/* Get users in sysadmin */
				SELECT
					LoginName				= sysServerPrincipalMember.name,
					SelectAccess			= 1,
					InsertAccess			= 1,
					UpdateAccess			= 1,
					DeleteAccess			= 1,
					DBOAccess				= 0,
					SysadminAccess			= 1
				FROM sys.server_principals AS sysServerPrincipalRole
				INNER JOIN sys.server_role_members AS sysServerRoleMember
					ON sysServerRoleMember.role_principal_id = sysServerPrincipalRole.principal_id
				INNER JOIN sys.server_principals AS sysServerPrincipalMember
					ON sysServerPrincipalMember.principal_id = sysServerRoleMember.member_principal_id
				WHERE sysServerPrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')
					AND sysServerPrincipalMember.is_disabled = 0
			) AS AccessSummary
		INNER JOIN master.dbo.syslogins AS syslogins
			ON syslogins.loginname = AccessSummary.LoginName
		WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT')
		GROUP BY
			AccessSummary.LoginName,
			CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END
				"
			}
		else
			{
				$UserDumpQueryText = "Unsupported SQL Server version"
			}

		if ($UserDumpQueryText -eq "Unsupported SQL Server version")
			{
				echo "$UserDumpQueryText on server $auditServerParam"
			}
		else
			{
				foreach ($dbAudit in $auditSMOserver.databases)
					{
						if ($dbAudit.IsAccessible)
							{
								echo "Gathering permissions from database $dbAudit on server $auditServerParam"

								$auditDBName = $dbAudit.Name
								$auditConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$auditServerParam; Initial Catalog=$auditDBName; Integrated Security=SSPI")
								$auditConn.Open()
								$auditCmd = $auditConn.CreateCommand()

								$auditCmd.CommandText = $UserDumpQueryText
								$queryresults = $auditCmd.ExecuteReader()

								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"]

										if ($row["LoginType"] -eq "SQL_USER")
											{
												$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 &gt; tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,
						InsertAccess		= CASE WHEN src.InsertAccess &gt; tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,
						UpdateAccess		= CASE WHEN src.UpdateAccess &gt; tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,
						DeleteAccess		= CASE WHEN src.DeleteAccess &gt; tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,
						DBOAccess			= CASE WHEN src.DBOAccess &gt; tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,
						SysadminAccess		= CASE WHEN src.SysadminAccess &gt; tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;
												"
												$null = $targetCmd.ExecuteNonQuery()
											}
										elseif ($row["LoginType"] -eq "WINDOWS_LOGIN")
											{
												$targetCmd.CommandText = "SELECT CacheCount = COUNT(*) FROM dbo.AuditDBUserCache WHERE UserName = '$insertLoginName'"
												$queryresults = $targetCmd.ExecuteReader()
												$queryresults.Read()
												$userCached = $queryresults["CacheCount"]
												$queryresults.Close()
												$queryresults.Dispose()

												if ($userCached -eq 0)
													{
														$ADUser = Get-QADUser $insertLoginName
														$insertFullName = $ADUser.name
														$insertUserName = $ADUser.ntaccountname

														$targetCmd.CommandText = "INSERT INTO dbo.AuditDBUserCache (UserName, FullName) VALUES ('$insertLoginName', '$insertFullName')"
														$null = $targetCmd.ExecuteNonQuery()
													}

												$targetCmd.CommandText = "
		MERGE dbo.AuditDBLogin AS tgt
		USING
			(
				SELECT
					ServerName				= '$insertServerName',
					Login					= UserName,
					Username				= UserName,
					FullName				= FullName,
					DatabaseName			= '$insertDatabaseName',
					SelectAccess			= $insertSelect,
					InsertAccess			= $insertInsert,
					UpdateAccess			= $insertUpdate,
					DeleteAccess			= $insertDelete,
					DBOAccess				= $insertDBO,
					SysadminAccess			= $insertSysadmin
				FROM dbo.AuditDBUserCache
				WHERE UserName = '$insertUserName'
			) 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 &gt; tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,
						InsertAccess		= CASE WHEN src.InsertAccess &gt; tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,
						UpdateAccess		= CASE WHEN src.UpdateAccess &gt; tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,
						DeleteAccess		= CASE WHEN src.DeleteAccess &gt; tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,
						DBOAccess			= CASE WHEN src.DBOAccess &gt; tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,
						SysadminAccess		= CASE WHEN src.SysadminAccess &gt; tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;
												"
												$null = $targetCmd.ExecuteNonQuery()
											}
										elseif ($row["LoginType"] -eq "WINDOWS_GROUP")
											{
												$targetCmd.CommandText = "SELECT CacheCount = COUNT(*) FROM dbo.AuditDBGroupCache WHERE Login = '$insertLoginName'"
												$queryresults = $targetCmd.ExecuteReader()
												$queryresults.Read()
												$loginCached = $queryresults["CacheCount"]
												$queryresults.Close()
												$queryresults.Dispose()

												if ($loginCached -eq 0)
													{
														$groupMembers = Get-QADGroupMember $insertLoginName -Indirect
														foreach ($member in $groupMembers)
															{
																$insertFullName = $member.name
																$insertUserName = $member.ntaccountname

																$targetCmd.CommandText = "
		MERGE dbo.AuditDBUserCache AS tgt
		USING
			(
				SELECT
					UserName = '$insertUserName',
					FullName = '$insertFullName'
			) AS src (UserName, FullName)
				ON tgt.UserName = src.UserName
			WHEN NOT MATCHED THEN
				INSERT (UserName, FullName)
					VALUES (src.UserName, src.FullName);
																"

																$null = $targetCmd.ExecuteNonQuery()

																$targetCmd.CommandText = "
		MERGE dbo.AuditDBGroupCache AS tgt
		USING
			(
				SELECT
					Login = '$insertLoginName',
					UserName = '$insertUserName'
			) AS src (Login, UserName)
				ON tgt.Login = src.Login
					AND tgt.UserName = src.UserName
			WHEN NOT MATCHED THEN
				INSERT (Login, UserName)
					VALUES (src.Login, src.UserName);
																"
																$null = $targetCmd.ExecuteNonQuery()
															}
													}
												$targetCmd.CommandText = "
		MERGE dbo.AuditDBLogin AS tgt
		USING
			(
				SELECT
					ServerName				= '$insertServerName',
					Login					= AuditDBGroupCache.Login,
					Username				= AuditDBUserCache.UserName,
					FullName				= AuditDBUserCache.FullName,
					DatabaseName			= '$insertDatabaseName',
					SelectAccess			= $insertSelect,
					InsertAccess			= $insertInsert,
					UpdateAccess			= $insertUpdate,
					DeleteAccess			= $insertDelete,
					DBOAccess				= $insertDBO,
					SysadminAccess			= $insertSysadmin
				FROM dbo.AuditDBUserCache
				INNER JOIN dbo.AuditDBGroupCache
					ON AuditDBGroupCache.Username = AuditDBUserCache.Username
				WHERE AuditDBGroupCache.Login = '$insertLoginName'
			) 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 &gt; tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,
						InsertAccess		= CASE WHEN src.InsertAccess &gt; tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,
						UpdateAccess		= CASE WHEN src.UpdateAccess &gt; tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,
						DeleteAccess		= CASE WHEN src.DeleteAccess &gt; tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,
						DBOAccess			= CASE WHEN src.DBOAccess &gt; tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,
						SysadminAccess		= CASE WHEN src.SysadminAccess &gt; tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;
												"
												$null = $targetCmd.ExecuteNonQuery()
											}
									}

								$auditConn.Close()
								$auditCmd.Dispose()
								$auditConn.Dispose()                        
							}
						else
							{
								echo "Database $dbAudit on server $auditServerParam is currently inaccessible"
							}
					}
			}
	}

$reportConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetServer; Initial Catalog=$targetDBName; Integrated Security=SSPI")
$reportConn.Open()
$reportCmd = $reportConn.CreateCommand()

$reportCmd.CommandText = "
SELECT ServerName, UserName, DatabaseName, DescriptionOfChange
FROM
	(
		SELECT
			AccessLevel = ROW_NUMBER() OVER(PARTITION BY ServerName, UserName ORDER BY AccessLevel),
			ServerName, UserName, DatabaseName, DescriptionOfChange
		FROM
			(
				/* New server logins */
				SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'new' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				EXCEPT
				SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'new' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				UNION
				/* Revoked server logins */
				SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				EXCEPT
				SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				UNION
				/* New read-only access */
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read granted' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE SelectAccess = 1
					AND COALESCE(DatabaseName, '') &lt;&gt; ''
				EXCEPT
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read granted' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE SelectAccess = 1
					AND COALESCE(DatabaseName, '') &lt;&gt; ''
				UNION
				/* Revoked read-only access */
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE SelectAccess = 1
					AND COALESCE(DatabaseName, '') &lt;&gt; ''
				EXCEPT
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE SelectAccess = 1
					AND COALESCE(DatabaseName, '') &lt;&gt; ''
				UNION
				/* New write access */
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write granted' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1
					AND COALESCE(DatabaseName, '') &lt;&gt; ''
				EXCEPT
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write granted' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1
					AND COALESCE(DatabaseName, '') &lt;&gt; ''
				UNION
				/* Revoked write access */
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1
					AND COALESCE(DatabaseName, '') &lt;&gt; ''
				EXCEPT
				SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1
					AND COALESCE(DatabaseName, '') &lt;&gt; ''
				UNION
				/* New DBO access */
				SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO granted' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE DBOAccess = 1
					AND COALESCE(DatabaseName, '') &lt;&gt; ''
				EXCEPT
				SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO granted' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE DBOAccess = 1
					AND COALESCE(DatabaseName, '') &lt;&gt; ''
				UNION
				/* Revoked DBO access */
				SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE DBOAccess = 1
					AND COALESCE(DatabaseName, '') &lt;&gt; ''
				EXCEPT
				SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE DBOAccess = 1
					AND COALESCE(DatabaseName, '') &lt;&gt; ''
				UNION
				/* New sysadmin access */
				SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN granted' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE SysadminAccess = 1
				EXCEPT
				SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN granted' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE SysadminAccess = 1
				UNION
				/* Revoked sysadmin access */
				SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLoginPrevious
				WHERE SysadminAccess = 1
				EXCEPT
				SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN revoked' AS DescriptionOfChange
				FROM dbo.AuditDBLogin
				WHERE SysadminAccess = 1
			) AS AllChanges
	) AS RankedChanges
WHERE AccessLevel = 1
	AND UserName &lt;&gt; ''
"

$queryresults = $reportCmd.ExecuteReader()

$emailbody = ""

foreach ($row in $queryresults)
	{
		$serverName = $row["ServerName"]
		$userName = $row["UserName"]
		$databaseName = $row["DatabaseName"]
		$descriptionOfChange = $row["DescriptionOfChange"]

		if ($descriptionOfChange -eq "new") 
			{
				$emailbody = $emailbody + '<b>New login ' + $userName + ' created on server ' + $serverName + '</b>'
			}
		elseif ($descriptionOfChange -eq "revoked") 
			{
				$emailbody = $emailbody + '<b>Login ' + $userName + ' dropped from server ' + $serverName + '</b>'
			}
		elseif ($descriptionOfChange -eq "SYSADMIN granted") 
			{
				$emailbody = $emailbody + '' + $userName + ' granted SYSADMIN rights to server ' + $serverName + ''
			}
		elseif ($descriptionOfChange -eq "SYSADMIN revoked") 
			{
				$emailbody = $emailbody + '' + $userName + ' SYSADMIN rights to server ' + $serverName + ' revoked'
			}
		elseif ($descriptionOfChange -eq "DBO granted") 
			{
				$emailbody = $emailbody + '' + $userName + ' granted DBO access to database ' + $databaseName + ' on server ' + $serverName + ''
			}
		elseif ($descriptionOfChange -eq "DBO revoked") 
			{
				$emailbody = $emailbody + '' + $userName + ' DBO access to database ' + $databaseName + ' revoked on server ' + $serverName + ''
			}
		elseif ($descriptionOfChange -eq "write granted") 
			{
				$emailbody = $emailbody + '' + $userName + ' granted WRITE access to database ' + $databaseName + ' on server ' + $serverName + ''
			}
		elseif ($descriptionOfChange -eq "write revoked") 
			{
				$emailbody = $emailbody + '' + $userName + ' WRITE access to database ' + $databaseName + ' revoked on server ' + $serverName + ''
			}
		elseif ($descriptionOfChange -eq "read revoked")
			{
				$emailbody = $emailbody + '' + $userName + ' READ access to database ' + $databaseName + ' revoked on server ' + $serverName + ''
			}
		else 
			{
				$emailbody = $emailbody + '' + $userName + ' granted READ access to database ' + $databaseName + ' on server ' + $serverName + ''
			}
	}

if ($emailbody)
	{
		$emailbody = 'The following changes to database access were detected:' + $emailbody
		$emailbody += ''

		#Send email
		$SMTPserver = "mail.pearson.com"
		$from = "pearsonvuedba@pearson.com"
		$to = "pearsonvuesqldba@pearson.com"
		$subject = "Database Access Changes Detected"

		$mailer = new-object Net.Mail.SMTPclient($SMTPserver)
		$msg = new-object Net.Mail.MailMessage($from, $to, $subject, $emailbody)
		$msg.IsBodyHTML = $true
		$mailer.send($msg)

		$msg.Dispose()
	}

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

Comments

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

Loading comments...