Showing newest 8 of 12 posts from September 2011. Show older posts
Showing newest 8 of 12 posts from September 2011. Show older posts

Thursday, September 29, 2011

Roll Your Own MDW, Sort Of

One of the reasons that I have this blog is to share things that I think are valuable to other SQL Server professionals.  Another reason is so that I can learn from the feedback that others provide, here and elsewhere.  Today is a perfect example.

A discussion broke out, as discussions are prone to do, on #sqlhelp this morning when the question was asked "How do I query sql server for long running/ intensive queries within time frame? (i.e This is after the fact, can't use profiler)".

Prior to SQL 2005, if you weren't actively capturing trace logs, the answer to this question would be "you can't".

SQL 2005 introduced us to DMV's (dynamic management views), system tables that are used by SQL Server to collect various metrics about server health and performance.  One of those DMV's, sys.dm_exec_query_stats, comes close to providing the answer, but doesn't quite go far enough.  I won't go into all of the reasons why, that's a lengthy topic of its own, and to be honest, I don't know all of the reasons.

During this conversation on Twitter, it was agreed that the DMV alone isn't enough, but the suggestion was made to make use of something called "MDW".  My first response was "What is MDW?".  That's when I learned about Maintenance Data Warehouse, a new feature of SQL 2008 that I had never heard of before.  It appears to be a mechanism for saving snapshots of the DMV data at various points in time, making that data more useful to people like the originator of the Twitter discussion. However, that's still not the point of this post.

The point of this post is to share a technique that I've been using for some time to make better use of the DMV data.  I've been logging, in 5-minute intervals, snapshots of the top 100 metrics from sys.dm_exec_query_stats, as ranked by reads, writes, duration, and CPU consumption.  It's not free, there is some server expense involved in collecting these statistics, but the value of having them far outweighs the cost.

First, you need a couple of tables, a view, and a trigger, to facilitate the long-term storage of this metrics data:

CREATE TABLE dbo.QueryStatsQueriesExecuted
	(
		QueryID				INT				NOT NULL	IDENTITY(1,1),
		QueryText			NVARCHAR(MAX)	NOT NULL,
		QueryTextChecksum	AS (CHECKSUM(QueryText)) PERSISTED,
		CONSTRAINT PK_QueryStatsQueriesExecuted PRIMARY KEY CLUSTERED (QueryID)
	)
GO

CREATE INDEX IX_QueryStatsQueriesExecuted_Checksum ON dbo.QueryStatsQueriesExecuted
	(QueryTextChecksum)
GO

CREATE TABLE dbo.QueryStatsExecutionStats
	(
		QueryStatsExecutionStatsID		INT			NOT NULL	IDENTITY(1,1),
		QueryID							INT			NOT NULL,
		ExecutionCount					BIGINT		NOT NULL,
		CPU								BIGINT		NOT NULL,
		Reads							BIGINT		NOT NULL,
		Writes							BIGINT		NOT NULL,
		Duration						BIGINT		NOT NULL,
		StatsDateTime					DATETIME	NOT NULL	DEFAULT (GETDATE()),
		CONSTRAINT PK_QueryStatsExecutionStats PRIMARY KEY CLUSTERED (QueryStatsExecutionStatsID),
		CONSTRAINT FK_QueryStatsExecutionStats_QueryStatsQueriesExecuted FOREIGN KEY(QueryID) REFERENCES dbo.QueryStatsQueriesExecuted(QueryID)
	)
GO

CREATE INDEX IX_QueryStatsExecutionStats_QueryID ON dbo.QueryStatsExecutionStats
	(QueryID);
	
CREATE INDEX IX_QueryStatsExecutionStats_StatsDateTime ON dbo.QueryStatsExecutionStats
	(StatsDateTime)
	INCLUDE (QueryID);
GO

CREATE VIEW dbo.QueryExecutionStats
AS
SELECT
	QueryStatsQueriesExecuted.QueryText,
	QueryStatsExecutionStats.ExecutionCount,
	QueryStatsExecutionStats.CPU,
	QueryStatsExecutionStats.Reads,
	QueryStatsExecutionStats.Writes,
	QueryStatsExecutionStats.Duration,
	QueryStatsExecutionStats.StatsDateTime
FROM dbo.QueryStatsExecutionStats
INNER JOIN dbo.QueryStatsQueriesExecuted
	ON QueryStatsExecutionStats.QueryID = QueryStatsQueriesExecuted.QueryID;
GO

CREATE TRIGGER dbo.QueryExecutionStats_Insert ON dbo.QueryExecutionStats
INSTEAD OF INSERT
AS
BEGIN

	MERGE dbo.QueryStatsQueriesExecuted
		USING 
			(
				SELECT DISTINCT QueryText
				FROM inserted
			) AS executedQueries
		ON QueryStatsQueriesExecuted.QueryTextChecksum = CHECKSUM(executedQueries.QueryText)
			AND QueryStatsQueriesExecuted.QueryText = executedQueries.QueryText
	WHEN NOT MATCHED THEN
		INSERT (QueryText)
		VALUES (executedQueries.QueryText);

	INSERT INTO dbo.QueryStatsExecutionStats
		(QueryID, ExecutionCount, CPU, Reads, Writes, Duration)
	SELECT
		QueryStatsQueriesExecuted.QueryID,
		inserted.ExecutionCount,
		inserted.CPU,
		inserted.Reads,
		inserted.Writes,
		inserted.Duration
	FROM inserted
	INNER JOIN dbo.QueryStatsQueriesExecuted
		ON QueryStatsQueriesExecuted.QueryTextChecksum = CHECKSUM(inserted.QueryText)
			AND QueryStatsQueriesExecuted.QueryText = inserted.QueryText;

END

Second, you need an Agent job, or some other mechanism, for running this query at regular intervals:

USE [VueSys]
GO
/****** Object:  StoredProcedure [dbo].[GetTop100QueryExecutionStats]    Script Date: 09/29/2011 09:22:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetTop100QueryExecutionStats]
AS
BEGIN

INSERT INTO dbo.QueryExecutionStats
	(QueryText, ExecutionCount, CPU, Reads, Writes, Duration)
SELECT
	QueryText,
	ExecutionCount,
	CPU,
	Reads,
	Writes,
	Duration
FROM
	(
		SELECT
			QueryText,
			ExecutionCount,
			CPU,
			Reads,
			Writes,
			Duration,
			Count_Ranking = RANK() OVER(ORDER BY ExecutionCount DESC),
			CPU_Ranking = RANK() OVER(ORDER BY CPU DESC),
			Reads_Ranking = RANK() OVER(ORDER BY Reads DESC),
			Writes_Ranking = RANK() OVER(ORDER BY Writes DESC),
			Duration_Ranking = RANK() OVER(ORDER BY Duration DESC)
		FROM
			(
				SELECT
					QueryText					= CASE WHEN LTRIM(querytext.text) LIKE 'CREATE %' THEN LTRIM(RTRIM(querytext.text)) ELSE dbo.vfnScrubTraceLine(querytext.text) END,
					ExecutionCount				= querystats.execution_count / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5),
					CPU							= querystats.total_worker_time / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5),
					Reads						= (querystats.total_physical_reads + querystats.total_logical_reads)  / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5),
					Writes						= querystats.total_logical_writes / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5),
					Duration					= querystats.total_elapsed_time / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5)
				FROM sys.dm_exec_query_stats AS querystats
				CROSS APPLY sys.dm_exec_sql_text(querystats.sql_handle) AS querytext
				WHERE querystats.last_execution_time >= DATEADD(MINUTE, -5, GETDATE())
					AND (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5) > 0
					AND querytext.text NOT LIKE '% sys.%'
					AND querytext.text NOT LIKE '% sp_%'
			) AS summary
	) AS ranked
WHERE ranked.CPU_Ranking <= 100
	OR ranked.Count_Ranking <= 100
	OR ranked.Reads_Ranking <= 100
	OR ranked.Writes_Ranking <= 100
	OR ranked.Duration_Ranking <= 100

END

It's not a perfect system, but it does allow me to see, in relative terms, the heavy-hitters on my server.  It also allows me to confirm that the index I added yesterday to the table CorrespondenceEventParameters really DID make an improvement in the way "that query" performs.

SELECT TOP 500 *
FROM VueSys.dbo.QueryExecutionStats
WHERE QueryText LIKE '%CorrespondenceEventParameters%'
	AND StatsDateTime < '9/27/2011 10:35am'
ORDER BY StatsDateTime DESC


Tuesday, September 27, 2011

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

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 + 'New login ' + $userName + ' created on server ' + $serverName + '
'
			}
		elseif ($descriptionOfChange -eq "revoked") 
			{
				$emailbody = $emailbody + 'Login ' + $userName + ' dropped from server ' + $serverName + '
'
			}
		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 <> '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 > 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()
											}
										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 > 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()
											}
										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 > 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()
											}
									}
													
								$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, '') <> ''
				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()

$emailbody = ""

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

		if ($descriptionOfChange -eq "new") 
			{
				$emailbody = $emailbody + 'New login ' + $userName + ' created on server ' + $serverName + '
'
			}
		elseif ($descriptionOfChange -eq "revoked") 
			{
				$emailbody = $emailbody + 'Login ' + $userName + ' dropped from server ' + $serverName + '
'
			}
		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()
	}

Thursday, September 22, 2011

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

If you've followed along to this point, and have assembled the pieces as I've given them to you, you're now looking at a Powershell script that is slowing getting more complex.

By now, you should be starting to appreciate how much power (pun intended) Powershell gives you.  It's quickly becoming one of my favorite tools, not only for interacting with SQL Server, but for tasks outside as well. It's great for OS-level operations, and with the proper extensions in place, can even interact with Active Directory.  That's what we're going to look at in this segment.

First, let's recap:

  • in Part 1, I gave you a SQL script to dump SQL Server 2005/2008 permissions
  • in Part 2, I gave you the same script for SQL 2000
  • in Part 3, I showed you how to wrap Powershell around those queries, in order to dump permissions from all databases
  • in Part 4, I showed you to save those permissions to a table

At this point, we have a script capable of dumping all of the database logins and their read/write/dbo/sa permissions for all databases on a given SQL Server instance.  Some of those database logins may be Active Directory domain groups, with multiple users belonging to them.  We can't see that from inside SQL Server, so if an auditor asks "Who's in this group?", we can't tell them.  With the Free Powershell Commands for Active Directory, a free "CmdLet" (an extension for Powershell) from Quest, we can.

With the CmdLet installed, this simple 2-line script will list the members of an Active Directory group:

Add-PSSnapin Quest.ActiveRoles.ADManagement
Get-QADGroupMember RealSQLAD\RealSQLGroup

Doesn't get any easier than that.  The real magic is yet to come, however, as we integrate that functionality into our audit script.  If you were paying attention during the last segment, you'll recall that I pointed out the "FullName" column.  If you've been paying even closer attention, you've noticed that the T-SQL statements from Part 1 and Part 2 return a column named "LoginType".  Today we're going to find out why those columns exist.

With all of this data available to us, there's no reason not to provide some sort of report, makes the management types happy.  Those same management types tend to prefer human names (who is "AD\miljk1"?).  Within our Powershell script, we can look at the "LoginType" column to see that a given database login is a Windows user, and then use the Quest CmdLet to fetch that user's human name from Active Directory:

if ($row["LoginType"] -eq "WINDOWS_LOGIN")
	{
		$ADUser = Get-QADUser $insertLoginName
		$insertFullName = $ADUser.name
		$insertUserName = $ADUser.ntaccountname
	}

That takes care of individual Windows users, but what about groups?  Almost as easy:

if ($row["LoginType"] -eq "WINDOWS_GROUP")
	{
		$groupMembers = Get-QADGroupMember $insertLoginName -Indirect
		foreach ($member in $groupMembers)
			{
				$insertFullName = $member.name
				$insertUserName = $member.ntaccountname
			}
	}

Put all of this together with our existing script, and here's what we have:

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

# Include Quest Active Directory CmdLet
Add-PSSnapin Quest.ActiveRoles.ADManagement
 
# 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 = "
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
		"
    }
# SQL Server is 2005 or greater
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 <> '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
		"
    }
# 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"]

								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 > 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()
									}
								elseif ($row["LoginType"] -eq "WINDOWS_LOGIN")
									{
										$ADUser = Get-QADUser $insertLoginName
										$insertFullName = $ADUser.name
										$insertUserName = $ADUser.ntaccountname

										$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()
									}
								elseif ($row["LoginType"] -eq "WINDOWS_GROUP")
									{
										$groupMembers = Get-QADGroupMember $insertLoginName -Indirect
										foreach ($member in $groupMembers)
											{
												$insertFullName = $member.name
												$insertUserName = $member.ntaccountname
												
												$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()

											}
									}
                            }
                    }
            }
    }

Monday, September 19, 2011

THE Interview Question

Not so long ago, in a galaxy not so far away, REAL SQL Guy was in need of a sidekick, a right-hand-man, a Robin to my Batman.  The "Help Wanted" sign was placed in the front window of the Guy-cave, and the interviews commenced.

REAL SQL Guy hates doing interviews, mostly because they turn out to be a waste of time, as yet another candidate turns out to be not-quite-DBA material.  "Can you spell S-Q-L?  No?  Door's over there."

As the process dragged on, and we continued to turn away the "I'm really good with Excel" and "it's time to stop delivering pizzas and get a real job" folks, frustration began to set in, and I started to think hard about the questions we were asking.

I finally settled on one question, just one, that would quickly filter out the guys who "used to have a SQL Server running on the network, somewhere".

"You're the on-call DBA, supporting, among other things, a 200-seat call center.  Each machine in that call center is running an application that reads and writes to your SQL Server database.  It's Friday afternoon, and you suddenly start getting calls from the Call Center users, each claiming that their application has hung.  There is no error message, the application has simply frozen.  What do you do?"

Some of the actual responses that were given to this question:

- use the Index Tuning Wizard to see if there's a missing index
- check to see if SQL Server has consumed all of the memory on the server, because it will do that sometimes
- have someone check each workstation to see if something is pegging the CPU
- use Profiler to record the server activity
- that's something our IT staff would handle

Of the dozen or more candidates that were asked this question, only two gave me the answer I was looking for:  sp_who2

In my opinion, under these circumstances, there is only one right answer.  Run sp_who2, look for a blocker, and kill that SPID.  That's the only reasonable solution when you have 200 call center agents, on the phone with customers, waiting for a resolution.

How would you answer the question?

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

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



Friday, September 16, 2011

Rasslin' With a Reluctant Restore

It's Friday, a day that I like to sit and reminisce (OK, stare out the window daydreaming).  While doing so, I saw somebody on the road outside doing something stupid, reminding me of a time when Stupidity made an appearance in my office.

All was right with the world, the universe was in order, and I think I was doing something, possibly staring out the window.  Suddenly the alarm was raised - the automated restore of one a database in our maintenance environment was failing, and the DBA responsible couldn't figure out why.  Faster than a speeding table scan, I spun around in my chair and grabbed my keyboard.

I began my interrogation of the victim - "What's the error message?"
"There isn't one", he replied, "The job just sits there and doesn't do anything!"

So much for advanced troubleshooting skills.  Firing up sp_who2, I saw that the RESTORE DATABASE command was being blocked (had been, in fact, for nearly three days) by another process.  This other process was stuck in a KILLED/ROLLBACK state, and belonged to none other than the DBA sitting beside me.


"What is this?", I asked.  He replied "Oh, that's something I killed earlier in the week, it shouldn't be there".  Well, it is, so let's figure out why.

Pulling DBCC INPUTBUFFER out of my, umm, utility belt, I found that this shouldn't-exist-but-does process had been running xp_cmdshell:

xp_cmdshell '\\ServerA\Import\SampleLiveData_ClientQuestions.txt'

Again I asked, "What is this?"
"Oh, I was trying to import a text file, I thought that might open it up within Management Studio" was the response.

Resisting the urge to destroy him with my Scowl Of Doom, I calmly replied, "No, what this did was launch Notepad as a background process, and it's sitting there waiting on input, which is why your non-existent process won't die."  To prove my point, I open up Windows Task Manager on the server:


I then further drove the point home by clicking the "End Process" button, dispatching NOTEPAD.EXE into the bit bucket.  Switching back to Management Studio and re-running sp_who2, as expected, the process-that-shouldn't-exist-but-does was no longer in existence.  More importantly, the RESTORE DATABASE process was no longer blocked, and was in fact actively generating disk I/O.  Less than an hour later, the restore was complete, and the universe was once again in order.

Thursday, September 15, 2011

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

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

Wednesday, September 14, 2011

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

In Part 1 of this series, I presented a T-SQL query that will return a summary of login permissions within a SQL 2005/2008 database.  I also mentioned that SQL 2000 requires a slightly different query.

Here is that query.

In the upcoming Part 3, I will start diving into the Powershell script that drives this process - stay tuned!




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