tag:blogger.com,1999:blog-21462768243402070202011-10-10T21:42:23.591-07:00Real SQL GuyTracy McKibbenhttp://www.blogger.com/profile/18326025369398096349noreply@blogger.comBlogger17125tag:blogger.com,1999:blog-2146276824340207020.post-13615930782188655512011-10-07T08:33:00.000-07:002011-10-07T08:33:34.645-07:00Loopty-Loop<div class="separator" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em; text-align: center;"><img border="0" height="140" src="http://2.bp.blogspot.com/-X3bOuj3ZAkU/TnOHKstiXoI/AAAAAAAAABA/hbRD_R9dQK0/s200/wtf.jpg" width="200" /></div>A recent encounter with my arch-enemy, The Tangled Loop, left me weary and scarred, I haven't yet fully recovered. &nbsp;During the battle, I witnessed unspeakable things, lines of code that mangled T-SQL into something unrecognizable. &nbsp;I feel guilty exposing you all to this, but it must be done, for you must know what to be on the lookout for. &nbsp;The Tangled Loop is still out there, somewhere, he may surface in your workplace next. &nbsp;What you're about to see is real, actual code that a developer wanted to deploy to our production server.<br /><br />The code below, and I use the term "code" loosely, is intended to loop (yes, that's right, loop) through a table, and for certain rows, replace certain string combinations with something else. &nbsp;Go ahead, look at it. &nbsp;I'll give you moment to stop vomiting and catch your breath.<br /><br /><pre class="brush: sql">-------------------------------------<br />-- declare variables<br />-------------------------------------<br />DECLARE @conversion TABLE (<br /> fromvalue VARCHAR(50),<br /> tovalue VARCHAR(50))<br />DECLARE @rowsToUpdate TABLE (<br /> idtoupdate INT,<br /> paramtoupdate VARCHAR(255))<br />DECLARE @paramName VARCHAR(20)<br />DECLARE @ruleID INT<br />DECLARE @ruleParam VARCHAR(255)<br />DECLARE @delim CHAR<br />DECLARE @delimIndex INT<br />DECLARE @delimWork VARCHAR(255)<br />DECLARE @oldValue VARCHAR(50)<br />DECLARE @newValue VARCHAR(50)<br />DECLARE @newValues VARCHAR(255)<br /><br />---------------------------------------<br />---- Initialize variables<br />---------------------------------------<br />INSERT INTO @conversion<br />VALUES ('E',<br /> 'EMAIL')<br /><br />INSERT INTO @conversion<br />VALUES ('S',<br /> 'SMS')<br /><br />INSERT INTO @conversion<br />VALUES ('X',<br /> 'DO_NOT_Send')<br /><br />INSERT INTO @conversion<br />VALUES ('EMAIL',<br /> 'EMAIL')<br /><br />INSERT INTO @conversion<br />VALUES ('SMS',<br /> 'SMS')<br /><br />INSERT INTO @conversion<br />VALUES ('DO_NOT_Send',<br /> 'DO_NOT_Send')<br /><br />SET @delim = ','<br />SET @paramName = 'CP='<br /><br />-------------------------------------<br />-- Procedure body<br />-------------------------------------<br />-- find all rows to process<br />INSERT INTO @rowsToUpdate<br />SELECT correspondencetemplateconfigurationselectionruleid,<br /> ruleparam<br />FROM dbo.correspondencetemplateconfigurationselectionrule<br />WHERE ruleparam LIKE @paramName + '%'<br /><br />-- loop through the rows to process<br />SET rowcount 1<br /><br />SELECT @ruleID = idtoupdate,<br /> @ruleParam = paramtoupdate<br />FROM @rowsToUpdate<br /><br />WHILE @@ROWCOUNT &lt;&gt; 0<br /> BEGIN<br /> PRINT N'Change: ' + CAST(@ruleParam AS VARCHAR(255))<br /><br /> -- The rule parameter is like: CP=a,b,c First we pull out the value portion (a,b,c)<br /> SET @delimWork = Substring(@ruleParam, Len(@paramName) + 1,<br /> Len(@ruleParam))<br /> SET @newValues = @paramName<br /><br /> -- loop through value list by delimiter (ex: if values are a,b,c then we process a then b then c)<br /> SELECT @delimIndex = Charindex(@delim, @delimWork, 0)<br /><br /> WHILE @delimIndex &gt; 0<br /> BEGIN<br /> SELECT @oldValue = Ltrim(Rtrim(Substring(@delimWork, 0, @delimIndex)<br /> ))<br /><br /> -- convert the old value to new new value<br /> SET @newValue = NULL<br /><br /> SELECT @newValue = tovalue<br /> FROM @conversion<br /> WHERE fromvalue = @oldValue<br /><br /> IF ( @newValue IS NOT NULL )<br /> BEGIN<br /> -- save new value to new list of values<br /> SET @newValues = @newValues + Ltrim(Rtrim(@newValue)) + @delim<br /> END<br /><br /> -- setup for the next loop of delimited values<br /> SELECT @delimWork = Substring(@delimWork, @delimIndex + 1, Len(<br /> @delimWork)<br /> )<br /><br /> SELECT @delimIndex = Charindex(@delim, @delimWork, 0)<br /> END<br /><br /> -- need to pull the last item out of the value list<br /> IF Len(@delimWork) &gt; 0<br /> BEGIN<br /> SET @oldValue = @delimWork<br /> -- convert the old value to new new value<br /> SET @newValue = NULL<br /><br /> SELECT @newValue = tovalue<br /> FROM @conversion<br /> WHERE fromvalue = @oldValue<br /><br /> IF ( @newValue IS NOT NULL )<br /> BEGIN<br /> -- save new value to new list of values<br /> SET @newValues = @newValues + Ltrim(Rtrim(@newValue))<br /> END<br /> END<br /><br /> PRINT 'To: ' + @newValues<br /><br /> PRINT '------------------------------------------'<br /><br /> -- Update the table<br /> UPDATE dbo.correspondencetemplateconfigurationselectionrule<br /> SET ruleparam = @newValues<br /> WHERE correspondencetemplateconfigurationselectionruleid = @ruleID<br /><br /> -- setup next loop of rows to update<br /> DELETE FROM @rowsToUpdate<br /> WHERE idtoupdate = @ruleID<br /><br /> SELECT @ruleID = idtoupdate,<br /> @ruleParam = paramtoupdate<br /> FROM @rowsToUpdate<br /> END<br /><br />SET rowcount 0 <br /></pre><br /><br />We all know looping is not preferred within T-SQL, but this example takes it to the extreme. &nbsp;Not only does it loop through the rows of a table, but when doing the string replacements, it loops through the strings, ONE CHARACTER AT A TIME!<br /><br />When The Tangled Loop launched this attack, I grabbed the nearest T-SQL reference book to use as a shield. &nbsp;This brief respite allowed me the time to muster up a counter-attack:<br /><br /><pre class="brush: sql">UPDATE dbo.correspondenceconfiguration<br />SET param = 'CP=' + REPLACE((SELECT map.newvalue AS [data()]<br /> FROM (SELECT '|E|' AS oldvalue,<br /> 'EMAIL' AS newvalue<br /> UNION ALL<br /> SELECT '|S|' AS oldvalue,<br /> 'SMS' AS newvalue<br /> UNION ALL<br /> SELECT '|X|' AS oldvalue,<br /> 'DO_NOT_Send' AS newvalue) AS map<br /> WHERE REPLACE(REPLACE(<br /> correspondenceconfiguration.param + '|',<br /> '=',<br /> '=|'), ','<br /> , '|,|')<br /> LIKE '%' + map.oldvalue + '%'<br /> FOR XML PATH('')), ' ', ',')<br />FROM dbo.correspondenceconfiguration<br />WHERE param LIKE 'CP=%'<br /></pre><br /><br />The server was saved from a terrible fate, but unfortunately, The Tangled Loop escaped, and is still out there, somewhere. &nbsp;I'm sure we haven't seen the last of him.<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-1361593078218865551?l=www.real-sql-guy.com' alt='' /></div>REAL SQL Guyhttp://www.blogger.com/profile/07486104775304135831noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-85361538236066978322011-10-07T07:21:00.000-07:002011-10-07T07:26:25.446-07:00Automated Source Control: A Librarian For Your Database Objects, Part 2<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-5A_l32grP68/ToytGNvoZbI/AAAAAAAAIlg/qJ_jS95PRis/s1600/librarian.gif" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="200" src="http://3.bp.blogspot.com/-5A_l32grP68/ToytGNvoZbI/AAAAAAAAIlg/qJ_jS95PRis/s200/librarian.gif" width="185" /></a></div>I assume that by now you've tried the script from <a href="http://www.real-sql-guy.com/2011/10/automated-source-control-librarian-for.html">Part 1</a>, and have seen how easy it is to script out your database objects using Powershell.&nbsp; The resulting scripts are the standard DDL scripts that you can generate from Management Studio, and for most of us, are just fine right out of the box.<br /><br />What if, however, we need to customize those scripts?&nbsp; In my environment, our software deployments (including the database scripts) are automated, and we need certain elements to be included in the scripts for the automation to function properly.&nbsp; I also like to include a certain degree of fail-safe in the scripts, to prevent accidentally deploying an object to the wrong database, or possibly wiping out a table full of data.<br /><br />Turns out that it's a snap to customize the scripts that are generated, using simple text piping, just like you've been doing in batch files for a decade or two.&nbsp; Simply insert the following into the script from <a href="http://www.real-sql-guy.com/2011/10/automated-source-control-librarian-for.html">Part 1</a>.&nbsp; For your convenience, the entire script is repeated at the end of this post.<br /><br />I'll resume this series after SQL Pass.&nbsp; I'm not attending, but I will be taking some time off next week, and with all of the buzz around the conference, nobody will be paying attention to little ol' me anyway! <br /><br /><pre class="brush: powershell"><br /> #Add commented USE statement for deployment validation tool<br /> "USE $dbname" > "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /><br /> #Add existing object handler (drop procedure, table exists, etc..)<br /> if ($TypeFolder -eq "StoredProcedure") {<br /> "IF OBJECT_ID('$ScriptFile', 'P') IS NOT NULL<br /> BEGIN<br /> <br /> DROP PROCEDURE $ScriptFile<br /> <br /> END<br /> GO<br /> " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /> } elseif ($TypeFolder -eq "View") {<br /> "IF OBJECT_ID('$ScriptFile', 'V') IS NOT NULL<br /> BEGIN<br /> <br /> DROP VIEW $ScriptFile<br /> <br /> END<br /> GO<br /> " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /> } elseif ($TypeFolder -eq "UserDefinedFunction") {<br /> "IF OBJECT_ID('$ScriptFile', 'FN') IS NOT NULL OR OBJECT_ID('$ScriptFile', 'TF') IS NOT NULL<br /> BEGIN<br /> <br /> DROP FUNCTION $ScriptFile<br /> <br /> END<br /> GO<br /> " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /> } elseif ($TypeFolder -eq "Trigger") {<br /> "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL<br /> BEGIN<br /> <br /> DROP TRIGGER $ScriptFile<br /> <br /> END<br /> GO<br /> " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /> } elseif ($TypeFolder -eq "Table") {<br /> "IF OBJECT_ID('$ScriptFile', 'U') IS NOT NULL<br /> BEGIN<br /> <br /> IF EXISTS(SELECT * FROM $ScriptFile)<br /> BEGIN<br /> <br /> USE tempdb<br /> RAISERROR('Table exists and contains data, cannot proceed.', 16, 1)<br /> RETURN<br /> <br /> END<br /> ELSE<br /> BEGIN<br /> <br /> DROP TABLE $ScriptFile<br /> <br /> END<br /> END<br /> GO<br /> " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /> }<br /></pre><br />The complete script now looks like this:<br /><br /><pre class="brush: powershell"><br />#Set server and database names here<br />$server = "REALServer"<br />$dbname = "REALDB"<br /><br />#Create SMO connection to DB<br />[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null<br /> $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server<br /> $db = $SMOserver.databases[$dbname]<br /> <br />#Build collection of objects to script<br />$Objects = $db.Tables<br />$Objects += $db.Views<br />$Objects += $db.StoredProcedures<br />$Objects += $db.UserDefinedFunctions<br /><br />#Set destination for generated scripts<br />$SavePath = "C:\temp\db\Source\" + $($dbname)<br /><br />#Loop through the object collection<br />foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {<br /> #Create Scripter object<br /> $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)<br /> <br /> #Set options for generated scripts<br /> $scriptr.Options.AppendToFile = $True<br /> $scriptr.Options.AllowSystemObjects = $False<br /> $scriptr.Options.ClusteredIndexes = $True<br /> $scriptr.Options.DriAll = $True<br /> $scriptr.Options.ScriptDrops = $False<br /> $scriptr.Options.IncludeHeaders = $False<br /> $scriptr.Options.ToFileOnly = $True<br /> $scriptr.Options.Indexes = $True<br /> $scriptr.Options.Permissions = $True<br /> $scriptr.Options.WithDependencies = $False<br /><br /> #Create destination folder and subfolder, if necessary<br /> $TypeFolder=$ScriptThis.GetType().Name<br /> if ((Test-Path -Path "$SavePath\$TypeFolder") -eq "true")<br /> {"Scripting Out $TypeFolder $ScriptThis"}<br /> else {new-item -type directory -name "$TypeFolder"-path "$SavePath"}<br /> $ScriptFile = $ScriptThis -replace "\[|\]"<br /><br /> #Set output filename for script<br /> $scriptr.Options.FileName = "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /><br /> #Add commented USE statement for deployment validation tool<br /> "USE $dbname" > "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /><br /> #Add existing object handler (drop procedure, table exists, etc..)<br /> if ($TypeFolder -eq "StoredProcedure") {<br /> "IF OBJECT_ID('$ScriptFile', 'P') IS NOT NULL<br /> BEGIN<br /> <br /> DROP PROCEDURE $ScriptFile<br /> <br /> END<br /> GO<br /> " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /> } elseif ($TypeFolder -eq "View") {<br /> "IF OBJECT_ID('$ScriptFile', 'V') IS NOT NULL<br /> BEGIN<br /> <br /> DROP VIEW $ScriptFile<br /> <br /> END<br /> GO<br /> " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /> } elseif ($TypeFolder -eq "UserDefinedFunction") {<br /> "IF OBJECT_ID('$ScriptFile', 'FN') IS NOT NULL OR OBJECT_ID('$ScriptFile', 'TF') IS NOT NULL<br /> BEGIN<br /> <br /> DROP FUNCTION $ScriptFile<br /> <br /> END<br /> GO<br /> " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /> } elseif ($TypeFolder -eq "Trigger") {<br /> "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL<br /> BEGIN<br /> <br /> DROP TRIGGER $ScriptFile<br /> <br /> END<br /> GO<br /> " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /> } elseif ($TypeFolder -eq "Table") {<br /> "IF OBJECT_ID('$ScriptFile', 'U') IS NOT NULL<br /> BEGIN<br /> <br /> IF EXISTS(SELECT * FROM $ScriptFile)<br /> BEGIN<br /> <br /> USE tempdb<br /> RAISERROR('Table exists and contains data, cannot proceed.', 16, 1)<br /> RETURN<br /> <br /> END<br /> ELSE<br /> BEGIN<br /> <br /> DROP TABLE $ScriptFile<br /> <br /> END<br /> END<br /> GO<br /> " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /> }<br /><br /> #Generate script for this object<br /> $scriptr.Script($ScriptThis)<br />}<br /><br />#Second loop through tables to script triggers<br />foreach ($Table in $db.Tables | where {!($_.IsSystemObject)}) {<br /> #Loop through triggers on this table<br /> foreach ($ScriptThis in $Table.Triggers) {<br /> #Create Scripter object<br /> $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)<br /> <br /> #Set options for generated scripts<br /> $scriptr.Options.AppendToFile = $True<br /> $scriptr.Options.AllowSystemObjects = $False<br /> $scriptr.Options.ClusteredIndexes = $True<br /> $scriptr.Options.DriAll = $True<br /> $scriptr.Options.ScriptDrops = $False<br /> $scriptr.Options.IncludeHeaders = $False<br /> $scriptr.Options.ToFileOnly = $True<br /> $scriptr.Options.Indexes = $True<br /> $scriptr.Options.Permissions = $True<br /> $scriptr.Options.WithDependencies = $False<br /> <br /> #Create destination folder and subfolder, if necessary<br /> $TypeFolder=$ScriptThis.GetType().Name<br /> if ((Test-Path -Path "$SavePath\Trigger") -eq "true")<br /> {"Scripting Out Trigger $ScriptThis"}<br /> else {new-item -type directory -name "Trigger"-path "$SavePath"}<br /> $ScriptFile = $ScriptThis -replace "\[|\]"<br /><br /> #Set output filename for script<br /> $scriptr.Options.FileName = "$SavePath\Trigger\$ScriptFile.SQL"<br /><br /> #Add commented USE statement for deployment validation tool<br /> "USE $dbname" > "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /><br /> #Add existing object handler (drop trigger, etc..)<br /> "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL<br /> BEGIN<br /> <br /> DROP TRIGGER $ScriptFile<br /> <br /> END<br /> GO<br /> " >> "$SavePath\Trigger\$ScriptFile.SQL"<br /><br /> #Generate script for this trigger<br /> $scriptr.Script($ScriptThis)<br /> }<br />}<br /></pre><br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-8536153823606697832?l=www.real-sql-guy.com' alt='' /></div>Tracy McKibbenhttp://www.blogger.com/profile/18326025369398096349noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-55909932873299326422011-10-05T12:29:00.000-07:002011-10-05T12:29:34.604-07:00Automated Source Control: A Librarian For Your Database Objects, Part 1<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-5A_l32grP68/ToytGNvoZbI/AAAAAAAAIlg/qJ_jS95PRis/s1600/librarian.gif" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="200" src="http://3.bp.blogspot.com/-5A_l32grP68/ToytGNvoZbI/AAAAAAAAIlg/qJ_jS95PRis/s200/librarian.gif" width="185" /></a></div>One of the biggest headaches in most development groups that do any sort of SQL Server work is that of source control.&nbsp; There are some commercial products that attempt to solve this headache, and from what I understand, some of them do a pretty decent job.&nbsp; The only one that I have personal experience with is Microsoft's Team Foundation doohickey, and I walked away from that unimpressed.<br /><br />Being a "do-it-yourself" kind of guy, and looking for new and interesting ways to use Powershell, I've built a (pretty slick, if I say so myself) method of AUTOMATICALLY version-controlling my database objects.&nbsp; No muss, no fuss, it just works, and nobody needs to remember to do anything.<br /><br />This will be the first of a series of articles describing what my process does, how it does it, why it does it, and at the end, you'll have a functional Powershell script that you can use yourself.&nbsp; All of my examples are specific to <a href="http://www.perforce.com/">Perforce</a>, since that's what we use here, but should be easily adaptable to your own system.<br /><br />Powershell makes it very easy to connect to a SQL Server database, and to manipulate the objects within that database.&nbsp; Virtually anything that you can do through Management Studio, you can also do through Powershell.<br /><br />Below is the beginnings of our source control script.&nbsp; In this example, the script will simply connect to the database of your choice, and will loop through certain objects in that database, generating SQL scripts for those objects.<br /><br /><br /><pre class="brush: powershell"><br />#Set server and database names here<br />$server = "REALServer"<br />$dbname = "REALDB"<br /><br />#Create SMO connection to DB<br />[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null<br /> $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server<br /> $db = $SMOserver.databases[$dbname]<br /> <br />#Build collection of objects to script<br />$Objects = $db.Tables<br />$Objects += $db.Views<br />$Objects += $db.StoredProcedures<br />$Objects += $db.UserDefinedFunctions<br /><br />#Set destination for generated scripts<br />$SavePath = "C:\temp\db\Source\" + $($dbname)<br /><br />#Loop through the object collection<br />foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {<br /> #Create Scripter object<br /> $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)<br /> <br /> #Set options for generated scripts<br /> $scriptr.Options.AppendToFile = $True<br /> $scriptr.Options.AllowSystemObjects = $False<br /> $scriptr.Options.ClusteredIndexes = $True<br /> $scriptr.Options.DriAll = $True<br /> $scriptr.Options.ScriptDrops = $False<br /> $scriptr.Options.IncludeHeaders = $False<br /> $scriptr.Options.ToFileOnly = $True<br /> $scriptr.Options.Indexes = $True<br /> $scriptr.Options.Permissions = $True<br /> $scriptr.Options.WithDependencies = $False<br /><br /> #Create destination folder and subfolder, if necessary<br /> $TypeFolder=$ScriptThis.GetType().Name<br /> if ((Test-Path -Path "$SavePath\$TypeFolder") -eq "true")<br /> {"Scripting Out $TypeFolder $ScriptThis"}<br /> else {new-item -type directory -name "$TypeFolder"-path "$SavePath"}<br /> $ScriptFile = $ScriptThis -replace "\[|\]"<br /><br /> #Set output filename for script<br /> $scriptr.Options.FileName = "$SavePath\$TypeFolder\$ScriptFile.SQL"<br /><br /> #Generate script for this object<br /> $scriptr.Script($ScriptThis)<br />}<br /><br />#Second loop through tables to script triggers<br />foreach ($Table in $db.Tables | where {!($_.IsSystemObject)}) {<br /> #Loop through triggers on this table<br /> foreach ($ScriptThis in $Table.Triggers) {<br /> #Create Scripter object<br /> $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)<br /> <br /> #Set options for generated scripts<br /> $scriptr.Options.AppendToFile = $True<br /> $scriptr.Options.AllowSystemObjects = $False<br /> $scriptr.Options.ClusteredIndexes = $True<br /> $scriptr.Options.DriAll = $True<br /> $scriptr.Options.ScriptDrops = $False<br /> $scriptr.Options.IncludeHeaders = $False<br /> $scriptr.Options.ToFileOnly = $True<br /> $scriptr.Options.Indexes = $True<br /> $scriptr.Options.Permissions = $True<br /> $scriptr.Options.WithDependencies = $False<br /> <br /> #Create destination folder and subfolder, if necessary<br /> $TypeFolder=$ScriptThis.GetType().Name<br /> if ((Test-Path -Path "$SavePath\Trigger") -eq "true")<br /> {"Scripting Out Trigger $ScriptThis"}<br /> else {new-item -type directory -name "Trigger"-path "$SavePath"}<br /> $ScriptFile = $ScriptThis -replace "\[|\]"<br /><br /> #Set output filename for script<br /> $scriptr.Options.FileName = "$SavePath\Trigger\$ScriptFile.SQL"<br /><br /> #Generate script for this trigger<br /> $scriptr.Script($ScriptThis)<br /> }<br />}<br /></pre></div><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-5590993287329932642?l=www.real-sql-guy.com' alt='' /></div>Tracy McKibbenhttp://www.blogger.com/profile/18326025369398096349noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-4080346431528033442011-10-05T07:58:00.000-07:002011-10-05T08:09:14.466-07:00Wonder Twin Powers, Activate!<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-un9NgRrzY-g/ToxwSUEifgI/AAAAAAAAACE/JUfWbeJtEco/s1600/fusion.jpg" imageanchor="1" style="clear:left; float:left;margin-right:1em; margin-bottom:1em"><img border="0" height="252" width="200" src="http://2.bp.blogspot.com/-un9NgRrzY-g/ToxwSUEifgI/AAAAAAAAACE/JUfWbeJtEco/s400/fusion.jpg" /></a></div>Every once in a while, two very different things will come together to produce something new, something unexpected, something awesome. Take these two guys that I know. One, a complete nerd, was bitten by a spider, and now spends his nights running around the city in tights, slinging strands of sticky goo all over the place. The other dude, also a nerd, who has always had a bit of a temper, got too close to the microwave or something, and now whenever he gets mad, turns into a muscular green no-nonsense kinda guy.<br><br>I recently had my own "coming together" experience, sadly nothing like these guys, I'm still a nerd, I don't turn green, and I refuse to wear tights. I did, however, develop a new ability through this experience - I can teleport, through time AND space! Ok, not me exactly, but I can teleport my work from one place and time to another. Seriously! By combining the teleportation powers of <a href="http://db.tt/bi2q2hP8">Dropbox</a> with the time-travelling abilities of <a href="http://www.ssmstoolspack.com/Download">SSMS Tools Pack</a>, I can leap from my current location to any other, and even backwards in time, to recover a SQL query or Management Studio session that I previously ran. This is an incredible new power!<br><br>My memory's not the greatest, inhibited by "shiny thing" syndrome and the ravages of age. There are often days where I leave work unfinished, intending to pick up where I left off the next day. To help remind myself of what I was doing and where I stopped, I'll leave Management Studio open on my laptop, put the laptop into sleep mode, and head home. Next morning, I simply wake the laptop up and pick up where I stopped the day before. This works well enough, but what happens if the laptop battery dies, or the laptop refuses to wake up? What if I want to continue working on my home machine, or on the "machine under the desk" that I have at work? Thanks to my new powers, these are no longer problems for me.<br><br>The first piece of this is <a href="http://db.tt/bi2q2hP8">Dropbox</a>. If you're not using it, you're missing out. Prior to discovering my new ability, I was using <a href="http://db.tt/bi2q2hP8">Dropbox</a> for all sorts of things. All of my utility scripts are in there, some eBooks, backups of my Android phone, lots of things. Start using it - you won't be sorry.<br><br>The second piece is the <a href="http://www.ssmstoolspack.com/Download">SSMS Tools Pack</a>, developed by Mladen Prajdic. This is a tool that extends Management Studio to do some very cool things. In particular, it automatically saves your Management Studio sessions and tabs, at regular intervals, making it possible to retrieve virtually any past query or session that you've run in Management Studio. By combining this capability, with the synching that <a href="http://db.tt/bi2q2hP8">Dropbox</a> offers, those past sessions and queries can now be accessed from any machine on which you're running <a href="http://db.tt/bi2q2hP8">Dropbox</a>. Pure genius!<br><br>Assuming that you have both <a href="http://db.tt/bi2q2hP8">Dropbox</a> and <a href="http://www.ssmstoolspack.com/Download">SSMS Tools Pack</a> installed, a simple configuration change is all that is needed to inherity my unique space/time-traveling ability:<br><br><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-fuFe5luPLhI/Toxu6GJHzlI/AAAAAAAAABs/Zr1xnd52JnE/s1600/ssmstools1.jpg" imageanchor="1" style=""><img border="0" height="344" width="400" src="http://2.bp.blogspot.com/-fuFe5luPLhI/Toxu6GJHzlI/AAAAAAAAABs/Zr1xnd52JnE/s400/ssmstools1.jpg" /></a></div><br><div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/-UpWbIO9gRok/ToxvEJyRapI/AAAAAAAAAB0/75ATkXEJ84k/s1600/ssmstools2.jpg" imageanchor="1" style=""><img border="0" height="344" width="400" src="http://4.bp.blogspot.com/-UpWbIO9gRok/ToxvEJyRapI/AAAAAAAAAB0/75ATkXEJ84k/s400/ssmstools2.jpg" /></a></div><br><br>Soon you'll see an SSMSTools folder appear in your <a href="http://db.tt/bi2q2hP8">Dropbox</a>. Make the same configuration change on your other workstations, and you now have integrated tab and session synchronization, across workstations!<br><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-MoTCLU6gUX0/ToxvK1ocaTI/AAAAAAAAAB8/osaZpksjssI/s1600/dropbox.jpg" imageanchor="1" style=""><img border="0" height="175" width="400" src="http://2.bp.blogspot.com/-MoTCLU6gUX0/ToxvK1ocaTI/AAAAAAAAAB8/osaZpksjssI/s400/dropbox.jpg" /></a></div><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-408034643152803344?l=www.real-sql-guy.com' alt='' /></div>REAL SQL Guyhttp://www.blogger.com/profile/07486104775304135831noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-60908921597368122272011-10-03T18:24:00.000-07:002011-10-03T18:27:25.565-07:00T-SQL Tuesday 23: Nested Joins<div class="separator" style="clear: both; text-align: center;"><a href="http://codegumbo.com/index.php/2011/09/27/tsql2sday-t-sql-tuesday-23early-edition" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://www.pearlknows.com/sitebuildercontent/sitebuilderpictures/T-SQLLogo.JPG" /></a></div>You don't see it mentioned very often, but it's one of my favorite tuning tricks - the nested join. &nbsp;There's nothing groundbreaking here, just my modest contribution to <a href="http://codegumbo.com/index.php/2011/09/27/tsql2sday-t-sql-tuesday-23early-edition">T-SQL Tuesday</a>.<br /><br />Allow me to present to you an actual query that surfaced on my production server today. &nbsp;On the surface, this looks like a query that could be stripped down and highly tuned. &nbsp;The complication is the&nbsp;<b>CandidateBiometricIdentityWorkStatus</b> table - there can be multiple rows in this table for a given&nbsp;<b>OrderItemID</b>, and we don't want to return ANY&nbsp;<b>OrderItemID </b>values that have a status of&nbsp;'NEW', 'IN_PROCESS', or 'ERROR'. &nbsp;In a classic case of non-set-based thinking, the developer produced this query:<br /><br /><pre class="brush: sql">UPDATE dbo.ResultUploadCompleteQueue<br />SET RequestMonitorID = 12<br />WHERE RequestMonitorID IS NULL<br /> AND OrderItemID IN<br /> (<br /> -- Find all OrderItemID in the work table that are ready for continued results processing<br /> SELECT DISTINCT work1.OrderItemID<br /> FROM dbo.CandidateBiometricIdentityWork work1<br /> INNER JOIN dbo.ResultUploadCompleteQueue resultQ<br /> ON work1.OrderItemID = resultQ.OrderItemID<br /> INNER JOIN dbo.CandidateBiometricIdentityWorkType AS workType<br /> ON work1.CandidateBiometricIdentityWorkTypeID = workType.CandidateBiometricIdentityWorkTypeID<br /> INNER JOIN dbo.CandidateBiometricIdentityRequestType AS requestType<br /> ON work1.CandidateBiometricIdentityRequestTypeID = requestType.CandidateBiometricIdentityRequestTypeID<br /> WHERE workType.WorkType = 'IDENTIFY' <br /> AND requestType.RequestType = 'BATCH'<br /> AND work1.InResultsProcessing = 1<br /> AND NOT EXISTS<br /> (<br /> -- Find all work for the OrderItemID that is not complete and exclude from results<br /> SELECT 1<br /> FROM dbo.CandidateBiometricIdentityWork AS work2<br /> INNER JOIN dbo.CandidateBiometricIdentityWorkType AS workType<br /> ON work2.CandidateBiometricIdentityWorkTypeID = workType.CandidateBiometricIdentityWorkTypeID<br /> INNER JOIN dbo.CandidateBiometricIdentityRequestType AS requestType<br /> ON work2.CandidateBiometricIdentityRequestTypeID = requestType.CandidateBiometricIdentityRequestTypeID<br /> INNER JOIN dbo.CandidateBiometricIdentityWorkStatus AS workStatus<br /> ON work2.CandidateBiometricIdentityWorkStatusID = workStatus.CandidateBiometricIdentityWorkStatusID<br /> WHERE work2.OrderItemID = work1.OrderItemID<br /> AND workType.WorkType = 'IDENTIFY' <br /> AND requestType.RequestType = 'BATCH'<br /> AND work2.InResultsProcessing = 1<br /> AND workStatus.status IN ('NEW','IN_PROCESS','ERROR')<br /> )<br /> )<br /></pre><br />This is the estimated plan for the query. &nbsp;Hard to follow, inefficient (Eager Spool, anyone), can be improved.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-Ug5_Zf8tJCc/ToIfTvb5BXI/AAAAAAAAIZI/a8X5UYJrsHo/s1600/badquery.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-Ug5_Zf8tJCc/ToIfTvb5BXI/AAAAAAAAIZI/a8X5UYJrsHo/s640/badquery.jpg" width="620" /></a></div><br /><br />Not only can the UPDATE be improved with a join, but the NOT IN filter can be replaced with, my favorite, a nested join. &nbsp;I rewrote the query, introduced the developer to nested joins (something he'd never seen before), and produced a much cleaner, much more efficient query plan.<br /><br /><pre class="brush: sql">UPDATE ResultUploadCompleteQueue<br />SET RequestMonitorID = 12<br />FROM dbo.CandidateBiometricIdentityWork<br />INNER JOIN dbo.ResultUploadCompleteQueue<br /> ON CandidateBiometricIdentityWork.OrderItemID = ResultUploadCompleteQueue.OrderItemID<br />INNER JOIN dbo.CandidateBiometricIdentityWorkType<br /> ON CandidateBiometricIdentityWork.CandidateBiometricIdentityWorkTypeID = CandidateBiometricIdentityWorkType.CandidateBiometricIdentityWorkTypeID<br />INNER JOIN dbo.CandidateBiometricIdentityRequestType<br /> ON CandidateBiometricIdentityWork.CandidateBiometricIdentityRequestTypeID = CandidateBiometricIdentityRequestType.CandidateBiometricIdentityRequestTypeID<br />LEFT JOIN dbo.CandidateBiometricIdentityWork AS CandidateBiometricIdentityWorkPending<br /> INNER JOIN dbo.CandidateBiometricIdentityWorkType AS CandidateBiometricIdentityWorkTypePending<br /> ON CandidateBiometricIdentityWorkPending.CandidateBiometricIdentityWorkTypeID = CandidateBiometricIdentityWorkTypePending.CandidateBiometricIdentityWorkTypeID<br /> INNER JOIN dbo.CandidateBiometricIdentityRequestType AS CandidateBiometricIdentityRequestTypePending<br /> ON CandidateBiometricIdentityWorkPending.CandidateBiometricIdentityRequestTypeID = CandidateBiometricIdentityRequestTypePending.CandidateBiometricIdentityRequestTypeID<br /> INNER JOIN dbo.CandidateBiometricIdentityWorkStatus AS CandidateBiometricIdentityWorkStatusPending<br /> ON CandidateBiometricIdentityWorkPending.CandidateBiometricIdentityWorkStatusID = CandidateBiometricIdentityWorkStatusPending.CandidateBiometricIdentityWorkStatusID<br /> ON CandidateBiometricIdentityWork.OrderItemID = CandidateBiometricIdentityWorkPending.OrderItemID<br /> AND CandidateBiometricIdentityWorkPending.InResultsProcessing = 1<br /> AND CandidateBiometricIdentityWorkTypePending.WorkType = 'IDENTIFY'<br /> AND CandidateBiometricIdentityRequestTypePending.RequestType = 'BATCH'<br /> AND CandidateBiometricIdentityWorkStatusPending.Status IN ('NEW','IN_PROCESS','ERROR')<br />WHERE CandidateBiometricIdentityWorkType.WorkType = 'IDENTIFY' <br /> AND CandidateBiometricIdentityRequestType.RequestType = 'BATCH'<br /> AND CandidateBiometricIdentityWork.InResultsProcessing = 1<br /> AND ResultUploadCompleteQueue.RequestMonitorID IS NULL<br /> AND CandidateBiometricIdentityWorkPending.OrderItemID IS NULL<br /></pre><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-bR6nldvcxho/ToIfc7qZOsI/AAAAAAAAIZM/m9JZTEQFWfE/s1600/goodquery.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-bR6nldvcxho/ToIfc7qZOsI/AAAAAAAAIZM/m9JZTEQFWfE/s640/goodquery.jpg" width="620" /></a></div><br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-6090892159736812227?l=www.real-sql-guy.com' alt='' /></div>Tracy McKibbenhttp://www.blogger.com/profile/18326025369398096349noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-53320690111926104962011-09-29T08:26:00.000-07:002011-09-29T08:26:47.500-07:00Roll Your Own MDW, Sort Of<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-1wHA9VolKfM/ToSFVQEqUbI/AAAAAAAAIcI/JCDdPaA4VoE/s1600/Bright_Idea.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="192" src="http://2.bp.blogspot.com/-1wHA9VolKfM/ToSFVQEqUbI/AAAAAAAAIcI/JCDdPaA4VoE/s200/Bright_Idea.jpg" width="200" /></a></div>One of the reasons that I have this blog is to share things that I think are valuable to other SQL Server professionals.&nbsp; Another reason is so that I can learn from the feedback that others provide, here and elsewhere.&nbsp; Today is a perfect example.<br /><br />A discussion broke out, as discussions are prone to do, on <a href="https://twitter.com/#%21/search/%23sqlhelp">#sqlhelp</a> 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)".<br /><br />Prior to SQL 2005, if you weren't actively capturing trace logs, the answer to this question would be "you can't".<br /><br />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.&nbsp; One of those DMV's, <a href="http://sys.dm_exec_query_stats/"><b>sys.dm_exec_query_stats</b></a>, comes close to providing the answer, but doesn't quite go far enough.&nbsp; 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.<br /><br />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".&nbsp; My first response was "What is MDW?".&nbsp; That's when I learned about <a href="http://www.sql-server-performance.com/2008/management-data-warehouse/">Maintenance Data Warehouse</a>, a new feature of SQL 2008 that I had never heard of before.&nbsp; 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.<br /><br />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.&nbsp; I've been logging, in 5-minute intervals, snapshots of the top 100 metrics from <b>sys.dm_exec_query_stats</b>, as ranked by reads, writes, duration, and CPU consumption.&nbsp; It's not free, there is some server expense involved in collecting these statistics, but the value of having them far outweighs the cost.<br /><br />First, you need a couple of tables, a view, and a trigger, to facilitate the long-term storage of this metrics data:<br /><br /><pre class="brush: sql"><br />CREATE TABLE dbo.QueryStatsQueriesExecuted<br /> (<br /> QueryID INT NOT NULL IDENTITY(1,1),<br /> QueryText NVARCHAR(MAX) NOT NULL,<br /> QueryTextChecksum AS (CHECKSUM(QueryText)) PERSISTED,<br /> CONSTRAINT PK_QueryStatsQueriesExecuted PRIMARY KEY CLUSTERED (QueryID)<br /> )<br />GO<br /><br />CREATE INDEX IX_QueryStatsQueriesExecuted_Checksum ON dbo.QueryStatsQueriesExecuted<br /> (QueryTextChecksum)<br />GO<br /><br />CREATE TABLE dbo.QueryStatsExecutionStats<br /> (<br /> QueryStatsExecutionStatsID INT NOT NULL IDENTITY(1,1),<br /> QueryID INT NOT NULL,<br /> ExecutionCount BIGINT NOT NULL,<br /> CPU BIGINT NOT NULL,<br /> Reads BIGINT NOT NULL,<br /> Writes BIGINT NOT NULL,<br /> Duration BIGINT NOT NULL,<br /> StatsDateTime DATETIME NOT NULL DEFAULT (GETDATE()),<br /> CONSTRAINT PK_QueryStatsExecutionStats PRIMARY KEY CLUSTERED (QueryStatsExecutionStatsID),<br /> CONSTRAINT FK_QueryStatsExecutionStats_QueryStatsQueriesExecuted FOREIGN KEY(QueryID) REFERENCES dbo.QueryStatsQueriesExecuted(QueryID)<br /> )<br />GO<br /><br />CREATE INDEX IX_QueryStatsExecutionStats_QueryID ON dbo.QueryStatsExecutionStats<br /> (QueryID);<br /> <br />CREATE INDEX IX_QueryStatsExecutionStats_StatsDateTime ON dbo.QueryStatsExecutionStats<br /> (StatsDateTime)<br /> INCLUDE (QueryID);<br />GO<br /><br />CREATE VIEW dbo.QueryExecutionStats<br />AS<br />SELECT<br /> QueryStatsQueriesExecuted.QueryText,<br /> QueryStatsExecutionStats.ExecutionCount,<br /> QueryStatsExecutionStats.CPU,<br /> QueryStatsExecutionStats.Reads,<br /> QueryStatsExecutionStats.Writes,<br /> QueryStatsExecutionStats.Duration,<br /> QueryStatsExecutionStats.StatsDateTime<br />FROM dbo.QueryStatsExecutionStats<br />INNER JOIN dbo.QueryStatsQueriesExecuted<br /> ON QueryStatsExecutionStats.QueryID = QueryStatsQueriesExecuted.QueryID;<br />GO<br /><br />CREATE TRIGGER dbo.QueryExecutionStats_Insert ON dbo.QueryExecutionStats<br />INSTEAD OF INSERT<br />AS<br />BEGIN<br /><br /> MERGE dbo.QueryStatsQueriesExecuted<br /> USING <br /> (<br /> SELECT DISTINCT QueryText<br /> FROM inserted<br /> ) AS executedQueries<br /> ON QueryStatsQueriesExecuted.QueryTextChecksum = CHECKSUM(executedQueries.QueryText)<br /> AND QueryStatsQueriesExecuted.QueryText = executedQueries.QueryText<br /> WHEN NOT MATCHED THEN<br /> INSERT (QueryText)<br /> VALUES (executedQueries.QueryText);<br /><br /> INSERT INTO dbo.QueryStatsExecutionStats<br /> (QueryID, ExecutionCount, CPU, Reads, Writes, Duration)<br /> SELECT<br /> QueryStatsQueriesExecuted.QueryID,<br /> inserted.ExecutionCount,<br /> inserted.CPU,<br /> inserted.Reads,<br /> inserted.Writes,<br /> inserted.Duration<br /> FROM inserted<br /> INNER JOIN dbo.QueryStatsQueriesExecuted<br /> ON QueryStatsQueriesExecuted.QueryTextChecksum = CHECKSUM(inserted.QueryText)<br /> AND QueryStatsQueriesExecuted.QueryText = inserted.QueryText;<br /><br />END<br /></pre><br />Second, you need an Agent job, or some other mechanism, for running this query at regular intervals:<br /><br /><pre class="brush: sql"><br />USE [VueSys]<br />GO<br />/****** Object: StoredProcedure [dbo].[GetTop100QueryExecutionStats] Script Date: 09/29/2011 09:22:35 ******/<br />SET ANSI_NULLS ON<br />GO<br />SET QUOTED_IDENTIFIER ON<br />GO<br />ALTER PROCEDURE [dbo].[GetTop100QueryExecutionStats]<br />AS<br />BEGIN<br /><br />INSERT INTO dbo.QueryExecutionStats<br /> (QueryText, ExecutionCount, CPU, Reads, Writes, Duration)<br />SELECT<br /> QueryText,<br /> ExecutionCount,<br /> CPU,<br /> Reads,<br /> Writes,<br /> Duration<br />FROM<br /> (<br /> SELECT<br /> QueryText,<br /> ExecutionCount,<br /> CPU,<br /> Reads,<br /> Writes,<br /> Duration,<br /> Count_Ranking = RANK() OVER(ORDER BY ExecutionCount DESC),<br /> CPU_Ranking = RANK() OVER(ORDER BY CPU DESC),<br /> Reads_Ranking = RANK() OVER(ORDER BY Reads DESC),<br /> Writes_Ranking = RANK() OVER(ORDER BY Writes DESC),<br /> Duration_Ranking = RANK() OVER(ORDER BY Duration DESC)<br /> FROM<br /> (<br /> SELECT<br /> QueryText = CASE WHEN LTRIM(querytext.text) LIKE 'CREATE %' THEN LTRIM(RTRIM(querytext.text)) ELSE dbo.vfnScrubTraceLine(querytext.text) END,<br /> ExecutionCount = querystats.execution_count / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5),<br /> CPU = querystats.total_worker_time / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5),<br /> Reads = (querystats.total_physical_reads + querystats.total_logical_reads) / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5),<br /> Writes = querystats.total_logical_writes / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5),<br /> Duration = querystats.total_elapsed_time / (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5)<br /> FROM sys.dm_exec_query_stats AS querystats<br /> CROSS APPLY sys.dm_exec_sql_text(querystats.sql_handle) AS querytext<br /> WHERE querystats.last_execution_time >= DATEADD(MINUTE, -5, GETDATE())<br /> AND (DATEDIFF(MINUTE, querystats.creation_time, GETDATE()) / 5) > 0<br /> AND querytext.text NOT LIKE '% sys.%'<br /> AND querytext.text NOT LIKE '% sp_%'<br /> ) AS summary<br /> ) AS ranked<br />WHERE ranked.CPU_Ranking <= 100<br /> OR ranked.Count_Ranking <= 100<br /> OR ranked.Reads_Ranking <= 100<br /> OR ranked.Writes_Ranking <= 100<br /> OR ranked.Duration_Ranking <= 100<br /><br />END<br /></pre><br />It's not a perfect system, but it does allow me to see, in relative terms, the heavy-hitters on my server.&nbsp; It also allows me to confirm that the index I added yesterday to the table <b>CorrespondenceEventParameters</b> really DID make an improvement in the way "that query" performs.<br /><br /><pre class="brush: sql"><br />SELECT TOP 500 *<br />FROM VueSys.dbo.QueryExecutionStats<br />WHERE QueryText LIKE '%CorrespondenceEventParameters%'<br /> AND StatsDateTime < '9/27/2011 10:35am'<br />ORDER BY StatsDateTime DESC<br /></pre><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-8EroFXSFO8Y/ToSM119MosI/AAAAAAAAIcM/8R5kzhByaoM/s1600/querystats.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-8EroFXSFO8Y/ToSM119MosI/AAAAAAAAIcM/8R5kzhByaoM/s640/querystats.jpg" width="620" /></a></div><br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-5332069011192610496?l=www.real-sql-guy.com' alt='' /></div>Tracy McKibbenhttp://www.blogger.com/profile/18326025369398096349noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-81111509600000913292011-09-27T10:46:00.000-07:002011-09-27T11:24:58.191-07:00Automated Permissions Auditing With Powershell and T-SQL: Part 6<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/-niYDNZDRZo0/Tm4kqMrG21I/AAAAAAAAH-k/lYXkMQexArg/s1600/spy.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-niYDNZDRZo0/Tm4kqMrG21I/AAAAAAAAH-k/lYXkMQexArg/s1600/spy.jpg" /></a></div>Everybody still with me? &nbsp;Have you tried the script from <a href="http://www.real-sql-guy.com/2011/09/automated-permissions-auditing-with_22.html">Part 5</a>? &nbsp;Did you notice how SLOW the Active Directory lookups are?<br /><br />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. &nbsp;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. &nbsp;If there was ever a case for caching, this is it. &nbsp;See where I'm going with this?<br /><br />Let's add caching to our script, to cut down on those redundant lookups. &nbsp;First, we need someplace to store the cached data. &nbsp;Someplace like, oh, a database table?<br /><br /><pre class="brush: powershell"># Create AuditDBUserCache table if it doesn't exist<br />$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));"<br />$null = $targetCmd.ExecuteNonQuery()<br /><br /># Create AuditDBGroupCache table if it doesn't exist<br />$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));"<br />$null = $targetCmd.ExecuteNonQuery()<br /><br /># Empty cache tables for reloading<br />$targetCmd.CommandText = "TRUNCATE TABLE dbo.AuditDBUserCache; TRUNCATE TABLE dbo.AuditDBGroupCache;"<br />$null = $targetCmd.ExecuteNonQuery()<br /></pre><br />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:<br /><br /><pre class="brush: powershell"># Cache individual user<br />$targetCmd.CommandText = "SELECT CacheCount = COUNT(*) FROM dbo.AuditDBUserCache WHERE UserName = '$insertLoginName'"<br />$queryresults = $targetCmd.ExecuteReader()<br />$queryresults.Read()<br />$userCached = $queryresults["CacheCount"]<br />$queryresults.Close()<br />$queryresults.Dispose()<br /><br /># Cache group<br />$targetCmd.CommandText = "SELECT CacheCount = COUNT(*) FROM dbo.AuditDBGroupCache WHERE Login = '$insertLoginName'"<br />$queryresults = $targetCmd.ExecuteReader()<br />$queryresults.Read()<br />$loginCached = $queryresults["CacheCount"]<br />$queryresults.Close()<br />$queryresults.Dispose()<br /></pre><br />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.<br /><br />So....<br /><br />We now have a (fast) script that will dump all of our database permissions into a table. &nbsp;Seems like we should do something to take advantage of that collected data, don't you think? &nbsp;Might I suggest that we build an email alerting mechanism, that will notify us when permissions changes are detected? &nbsp;C'mon, it's not hard to do!<br /><br />First, we need to create another table:<br /><br /><pre class="brush: powershell"># Create AuditDBLoginPrevious table if it doesn't exist<br />$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));"<br />$null = $targetCmd.ExecuteNonQuery()<br /></pre><br /><br />Each time the script is run, this table is purged, and the existing data from the last execution is moved here:<br /><div><br /></div><div><pre class="brush: powershell"># Archive rows in AuditDBLogin for this server<br />$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';"<br />$null = $targetCmd.ExecuteNonQuery()<br /></pre><div><br /></div><br />Second, we need a query that will compare the "old" data to the "new" data, and tell us what has changed:<br /><br /><pre class="brush: powershell">$reportConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetServerName; Initial Catalog=$targetDBName; Integrated Security=SSPI")<br />$reportConn.Open()<br />$reportCmd = $reportConn.CreateCommand()<br /><br />$reportCmd.CommandText = "<br />SELECT ServerName, UserName, DatabaseName, DescriptionOfChange<br />FROM<br /> (<br /> SELECT<br /> AccessLevel = ROW_NUMBER() OVER(PARTITION BY ServerName, UserName ORDER BY AccessLevel),<br /> ServerName, UserName, DatabaseName, DescriptionOfChange<br /> FROM<br /> (<br /> /* New server logins */<br /> SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'new' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> EXCEPT<br /> SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'new' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> UNION<br /> /* Revoked server logins */<br /> SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> EXCEPT<br /> SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> UNION<br /> /* New read-only access */<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE SelectAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> EXCEPT<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE SelectAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> UNION<br /> /* Revoked read-only access */<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE SelectAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> EXCEPT<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE SelectAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> UNION<br /> /* New write access */<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> EXCEPT<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> UNION<br /> /* Revoked write access */<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> EXCEPT<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> UNION<br /> /* New DBO access */<br /> SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE DBOAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> EXCEPT<br /> SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE DBOAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> UNION<br /> /* Revoked DBO access */<br /> SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE DBOAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> EXCEPT<br /> SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE DBOAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> UNION<br /> /* New sysadmin access */<br /> SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE SysadminAccess = 1<br /> EXCEPT<br /> SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE SysadminAccess = 1<br /> UNION<br /> /* Revoked sysadmin access */<br /> SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE SysadminAccess = 1<br /> EXCEPT<br /> SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE SysadminAccess = 1<br /> ) AS AllChanges<br /> ) AS RankedChanges<br />WHERE AccessLevel = 1<br /> AND UserName &lt;&gt; ''<br />"<br /><br />$queryresults = $reportCmd.ExecuteReader()<br /></pre><br />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:<br /><br /><pre class="brush: powershell">$emailbody = ""<br /><br />foreach ($row in $queryresults)<br /> {<br /> $serverName = $row["ServerName"]<br /> $userName = $row["UserName"]<br /> $databaseName = $row["DatabaseName"]<br /> $descriptionOfChange = $row["DescriptionOfChange"]<br /><br /> if ($descriptionOfChange -eq "new") <br /> {<br /> $emailbody = $emailbody + '<span style="color: black;"><b>New login ' + $userName + ' created on server ' + $serverName + '</b></span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "revoked") <br /> {<br /> $emailbody = $emailbody + '<span style="color: black;"><b>Login ' + $userName + ' dropped from server ' + $serverName + '</b></span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "SYSADMIN granted") <br /> {<br /> $emailbody = $emailbody + '<span style="color: red;">' + $userName + ' granted SYSADMIN rights to server ' + $serverName + '</span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "SYSADMIN revoked") <br /> {<br /> $emailbody = $emailbody + '<span style="color: red;">' + $userName + ' SYSADMIN rights to server ' + $serverName + ' revoked</span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "DBO granted") <br /> {<br /> $emailbody = $emailbody + '<span style="color: orange;">' + $userName + ' granted DBO access to database ' + $databaseName + ' on server ' + $serverName + '</span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "DBO revoked") <br /> {<br /> $emailbody = $emailbody + '<span style="color: orange;">' + $userName + ' DBO access to database ' + $databaseName + ' revoked on server ' + $serverName + '</span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "write granted") <br /> {<br /> $emailbody = $emailbody + '<span style="color: yellow;">' + $userName + ' granted WRITE access to database ' + $databaseName + ' on server ' + $serverName + '</span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "write revoked") <br /> {<br /> $emailbody = $emailbody + '<span style="color: yellow;">' + $userName + ' WRITE access to database ' + $databaseName + ' revoked on server ' + $serverName + '</span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "read revoked")<br /> {<br /> $emailbody = $emailbody + '<span style="color: black;">' + $userName + ' READ access to database ' + $databaseName + ' revoked on server ' + $serverName + '</span><br />'<br /> }<br /> else <br /> {<br /> $emailbody = $emailbody + '<span style="color: black;">' + $userName + ' granted READ access to database ' + $databaseName + ' on server ' + $serverName + '</span><br />'<br /> }<br /> }<br /><br />if ($emailbody)<br /> {<br /> $emailbody = 'The following changes to database access were detected:<br /><br />' + $emailbody<br /> $emailbody += ''<br /><br /> #Send email<br /> $SMTPserver = "real.smtp.server"<br /> $from = "real@real-sql-guy.com"<br /> $to = "real@real-sql-guy.com"<br /> $subject = "Database Access Changes Detected"<br /><br /> $mailer = new-object Net.Mail.SMTPclient($SMTPserver)<br /> $msg = new-object Net.Mail.MailMessage($from, $to, $subject, $emailbody)<br /> $msg.IsBodyHTML = $true<br /> $mailer.send($msg)<br /><br /> $msg.Dispose()<br /> }<br /></pre><br />Pretty easy, huh? &nbsp;Set this up to run on a schedule, and you have an automated babysitter keeping an eye out for unauthorized permissions changes. &nbsp;There's one more feature that I want to add to this, but I'll save that for the next episode.<br /><br />Here's the full script, so that you don't have to try inserting these changes yourself. &nbsp;Notice also that the script now accepts parameters - the target server, target database, and a comma-delimited list of servers to audit:<br /><br /><pre class="brush: powershell">param([string]$auditServerList, [string]$targetServer, [string]$targetDBName)<br /><br />Add-PSSnapin Quest.ActiveRoles.ADManagement<br /><br />if (!($auditServerList))<br /> {<br /> echo "No audit server(s) specified"<br /> break<br /> }<br /><br />if (!($targetServer))<br /> {<br /> echo "No target server specified"<br /> break<br /> }<br /><br />if (!($targetDBName))<br /> {<br /> echo "No target database specified"<br /> break<br /> }<br /><br />[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null<br />$targetSMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist "$targetServer"<br />$dbTarget = $targetSMOserver.databases[$targetDBName]<br /><br />$targetConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetServer; Initial Catalog=$targetDBName; Integrated Security=SSPI")<br />$targetConn.Open()<br />$targetCmd = $targetConn.CreateCommand()<br /><br />$Now = Get-Date<br /><br /># Create AuditDBLogin table if it doesn't exist<br />$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));"<br />$null = $targetCmd.ExecuteNonQuery()<br /> <br /># Create AuditDBLoginPrevious table if it doesn't exist<br />$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));"<br />$null = $targetCmd.ExecuteNonQuery()<br /><br /># Create AuditDBUserCache table if it doesn't exist<br />$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));"<br />$null = $targetCmd.ExecuteNonQuery()<br /><br /># Create AuditDBGroupCache table if it doesn't exist<br />$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));"<br />$null = $targetCmd.ExecuteNonQuery()<br /><br /># Empty cache tables for reloading<br />$targetCmd.CommandText = "TRUNCATE TABLE dbo.AuditDBUserCache; TRUNCATE TABLE dbo.AuditDBGroupCache;"<br />$null = $targetCmd.ExecuteNonQuery()<br /><br />foreach ($auditServerParam in $auditServerList.Split())<br /> {<br /> # Archive rows in AuditDBLogin for this server<br /> $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';"<br /> $null = $targetCmd.ExecuteNonQuery()<br /><br /> $memberList = @()<br /><br /> $auditSMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist "$auditServerParam"<br /><br /> if ($auditSMOServer.Information.VersionMajor -eq 8)<br /> {<br /> $UserDumpQueryText = "<br /> SELECT<br /> ServerName = @@SERVERNAME,<br /> LoginName = AccessSummary.LoginName,<br /> LoginType = CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,<br /> DatabaseName = DB_NAME(),<br /> SelectAccess = MAX(AccessSummary.SelectAccess),<br /> InsertAccess = MAX(AccessSummary.InsertAccess),<br /> UpdateAccess = MAX(AccessSummary.UpdateAccess),<br /> DeleteAccess = MAX(AccessSummary.DeleteAccess),<br /> DBOAccess = MAX(AccessSummary.DBOAccess),<br /> SysadminAccess = MAX(AccessSummary.SysadminAccess)<br /> FROM<br /> (<br /> /* Get logins with permissions */<br /> SELECT <br /> LoginName = sysusers.name,<br /> SelectAccess = CASE WHEN sysprotects.action = 193 THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN sysprotects.action = 195 THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN sysprotects.action = 196 THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN sysprotects.action = 197 THEN 1 ELSE 0 END,<br /> DBOAccess = 0,<br /> SysadminAccess = 0<br /> FROM dbo.sysobjects<br /> INNER JOIN dbo.sysprotects<br /> ON sysprotects.id = sysobjects.id<br /> INNER JOIN dbo.sysusers<br /> ON sysusers.uid = sysprotects.uid<br /> INNER JOIN master.dbo.syslogins AS syslogins<br /> ON syslogins.sid = sysusers.sid<br /> WHERE sysobjects.type IN ('U', 'V')<br /> AND sysusers.issqlrole = 0<br /> AND sysprotects.protecttype IN (204, 205)<br /> AND sysprotects.action IN (193, 195, 196, 197)<br /> UNION ALL<br /> /* Get group members with permissions */<br /> SELECT <br /> LoginName = sysusersMember.name,<br /> SelectAccess = CASE WHEN sysprotects.action = 193 THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN sysprotects.action = 195 THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN sysprotects.action = 196 THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN sysprotects.action = 197 THEN 1 ELSE 0 END,<br /> DBOAccess = 0,<br /> SysadminAccess = 0<br /> FROM dbo.sysobjects<br /> INNER JOIN dbo.sysprotects<br /> ON sysprotects.id = sysobjects.id<br /> INNER JOIN dbo.sysusers AS sysusersRole<br /> ON sysusersRole.uid = sysprotects.uid<br /> INNER JOIN dbo.sysmembers<br /> ON sysmembers.groupuid = sysusersRole.uid<br /> INNER JOIN dbo.sysusers AS sysusersMember<br /> ON sysusersMember.uid = sysmembers.memberuid<br /> INNER JOIN master.dbo.syslogins AS syslogins<br /> ON syslogins.sid = sysusersMember.sid<br /> WHERE sysobjects.type IN ('U', 'V')<br /> AND sysusersRole.issqlrole = 1<br /> AND sysusersRole.name NOT IN ('public')<br /> AND sysprotects.protecttype IN (204, 205)<br /> AND sysprotects.action IN (193, 195, 196, 197)<br /> UNION ALL<br /> /* Get users in db_owner, db_datareader and db_datawriter */<br /> SELECT<br /> LoginName = syslogins.name,<br /> SelectAccess = CASE WHEN sysusers.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> DBOAccess = CASE WHEN sysusers.name IN ('db_owner') THEN 1 ELSE 0 END,<br /> SysadminAccess = 0<br /> FROM dbo.sysusers<br /> INNER JOIN dbo.sysmembers<br /> ON sysmembers.groupuid = sysusers.uid<br /> INNER JOIN dbo.sysusers AS sysusersMember<br /> ON sysusersMember.uid = sysmembers.memberuid<br /> INNER JOIN master.dbo.syslogins AS syslogins<br /> ON syslogins.sid = sysusersMember.sid<br /> WHERE sysusers.name IN ('db_owner', 'db_datareader', 'db_datawriter')<br /> UNION ALL<br /> /* Get users in sysadmin */<br /> SELECT<br /> LoginName = syslogins.name,<br /> SelectAccess = 1,<br /> InsertAccess = 1,<br /> UpdateAccess = 1,<br /> DeleteAccess = 1,<br /> DBOAccess = 0,<br /> SysadminAccess = 1<br /> FROM master.dbo.syslogins AS syslogins<br /> WHERE syslogins.sysadmin = 1<br /> ) AS AccessSummary<br /> INNER JOIN master.dbo.syslogins AS syslogins<br /> ON syslogins.loginname = AccessSummary.LoginName<br /> WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT')<br /> GROUP BY<br /> AccessSummary.LoginName,<br /> CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END<br /> "<br /> }<br /> elseif ($auditSMOServer.Information.VersionMajor -gt 8)<br /> {<br /> $UserDumpQueryText = "<br /> SELECT<br /> ServerName = @@SERVERNAME,<br /> LoginName = AccessSummary.LoginName,<br /> LoginType = CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,<br /> DatabaseName = DB_NAME(),<br /> SelectAccess = MAX(AccessSummary.SelectAccess),<br /> InsertAccess = MAX(AccessSummary.InsertAccess),<br /> UpdateAccess = MAX(AccessSummary.UpdateAccess),<br /> DeleteAccess = MAX(AccessSummary.DeleteAccess),<br /> DBOAccess = MAX(AccessSummary.DBOAccess),<br /> SysadminAccess = MAX(AccessSummary.SysadminAccess)<br /> FROM<br /> (<br /> /* Get logins with permissions */<br /> SELECT <br /> LoginName = sysDatabasePrincipal.name,<br /> SelectAccess = CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,<br /> DBOAccess = 0,<br /> SysadminAccess = 0<br /> FROM sys.database_permissions AS sysDatabasePermission<br /> INNER JOIN sys.database_principals AS sysDatabasePrincipal<br /> ON sysDatabasePrincipal.principal_id = sysDatabasePermission.grantee_principal_id<br /> INNER JOIN sys.server_principals AS sysServerPrincipal<br /> ON sysServerPrincipal.sid = sysDatabasePrincipal.sid<br /> WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'<br /> AND sysDatabasePrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')<br /> AND sysServerPrincipal.is_disabled = 0<br /> UNION ALL<br /> /* Get group members with permissions */<br /> SELECT <br /> LoginName = sysDatabasePrincipalMember.name,<br /> SelectAccess = CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,<br /> DBOAccess = 0,<br /> SysadminAccess = 0<br /> FROM sys.database_permissions AS sysDatabasePermission<br /> INNER JOIN sys.database_principals AS sysDatabasePrincipalRole<br /> ON sysDatabasePrincipalRole.principal_id = sysDatabasePermission.grantee_principal_id<br /> INNER JOIN sys.database_role_members AS sysDatabaseRoleMember<br /> ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id<br /> INNER JOIN sys.database_principals AS sysDatabasePrincipalMember<br /> ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id<br /> INNER JOIN sys.server_principals AS sysServerPrincipal<br /> ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid<br /> WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'<br /> AND sysDatabasePrincipalRole.type_desc = 'DATABASE_ROLE'<br /> AND sysDatabasePrincipalRole.name &lt;&gt; 'public'<br /> AND sysDatabasePrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')<br /> AND sysServerPrincipal.is_disabled = 0<br /> UNION ALL<br /> /* Get users in db_owner, db_datareader and db_datawriter */<br /> SELECT<br /> LoginName = sysServerPrincipal.name,<br /> SelectAccess = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> DBOAccess = CASE WHEN sysDatabasePrincipalRole.name = 'db_owner' THEN 1 ELSE 0 END,<br /> SysadminAccess = 0<br /> FROM sys.database_principals AS sysDatabasePrincipalRole<br /> INNER JOIN sys.database_role_members AS sysDatabaseRoleMember<br /> ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id<br /> INNER JOIN sys.database_principals AS sysDatabasePrincipalMember<br /> ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id<br /> INNER JOIN sys.server_principals AS sysServerPrincipal<br /> ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid<br /> WHERE sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader', 'db_datawriter')<br /> AND sysServerPrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')<br /> AND sysServerPrincipal.is_disabled = 0<br /> UNION ALL<br /> /* Get users in sysadmin */<br /> SELECT<br /> LoginName = sysServerPrincipalMember.name,<br /> SelectAccess = 1,<br /> InsertAccess = 1,<br /> UpdateAccess = 1,<br /> DeleteAccess = 1,<br /> DBOAccess = 0,<br /> SysadminAccess = 1<br /> FROM sys.server_principals AS sysServerPrincipalRole<br /> INNER JOIN sys.server_role_members AS sysServerRoleMember<br /> ON sysServerRoleMember.role_principal_id = sysServerPrincipalRole.principal_id<br /> INNER JOIN sys.server_principals AS sysServerPrincipalMember<br /> ON sysServerPrincipalMember.principal_id = sysServerRoleMember.member_principal_id<br /> WHERE sysServerPrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')<br /> AND sysServerPrincipalMember.is_disabled = 0<br /> ) AS AccessSummary<br /> INNER JOIN master.dbo.syslogins AS syslogins<br /> ON syslogins.loginname = AccessSummary.LoginName<br /> WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT')<br /> GROUP BY<br /> AccessSummary.LoginName,<br /> CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END<br /> "<br /> }<br /> else<br /> {<br /> $UserDumpQueryText = "Unsupported SQL Server version"<br /> }<br /><br /> if ($UserDumpQueryText -eq "Unsupported SQL Server version")<br /> {<br /> echo "$UserDumpQueryText on server $auditServerParam"<br /> }<br /> else<br /> {<br /> foreach ($dbAudit in $auditSMOserver.databases)<br /> {<br /> if ($dbAudit.IsAccessible)<br /> {<br /> echo "Gathering permissions from database $dbAudit on server $auditServerParam"<br /><br /> $auditDBName = $dbAudit.Name<br /> $auditConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$auditServerParam; Initial Catalog=$auditDBName; Integrated Security=SSPI")<br /> $auditConn.Open()<br /> $auditCmd = $auditConn.CreateCommand()<br /><br /> $auditCmd.CommandText = $UserDumpQueryText<br /> $queryresults = $auditCmd.ExecuteReader()<br /><br /> foreach ($row in $queryresults)<br /> {<br /> $insertServerName = $row["ServerName"]<br /> $insertDatabaseName = $row["DatabaseName"]<br /> $insertLoginName = $row["LoginName"]<br /> $insertUserName = $row["LoginName"]<br /> $insertFullName = ""<br /> $insertSelect = $row["SelectAccess"]<br /> $insertInsert = $row["InsertAccess"]<br /> $insertUpdate = $row["UpdateAccess"]<br /> $insertDelete = $row["DeleteAccess"]<br /> $insertDBO = $row["DBOAccess"]<br /> $insertSysadmin = $row["SysadminAccess"]<br /><br /> if ($row["LoginType"] -eq "SQL_USER")<br /> {<br /> $targetCmd.CommandText = "<br /> MERGE dbo.AuditDBLogin AS tgt<br /> USING<br /> (<br /> SELECT<br /> ServerName = '$insertServerName',<br /> Login = '$insertLoginName',<br /> Username = '$insertUsername',<br /> FullName = '$insertFullName',<br /> DatabaseName = '$insertDatabaseName',<br /> SelectAccess = $insertSelect,<br /> InsertAccess = $insertInsert,<br /> UpdateAccess = $insertUpdate,<br /> DeleteAccess = $insertDelete,<br /> DBOAccess = $insertDBO,<br /> SysadminAccess = $insertSysadmin<br /> ) AS src (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> ON <br /> (<br /> tgt.ServerName = src.ServerName<br /> AND tgt.Login = src.Login<br /> AND tgt.Username = src.Username<br /> AND tgt.FullName = src.FullName<br /> AND tgt.DatabaseName = src.DatabaseName<br /> )<br /> WHEN NOT MATCHED THEN<br /> INSERT (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> VALUES (src.ServerName, src.Login, src.Username, src.FullName, src.DatabaseName, src.SelectAccess, src.InsertAccess, src.UpdateAccess, src.DeleteAccess, src.DBOAccess, src.SysadminAccess)<br /> WHEN MATCHED THEN<br /> UPDATE<br /> SET<br /> SelectAccess = CASE WHEN src.SelectAccess &gt; tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,<br /> InsertAccess = CASE WHEN src.InsertAccess &gt; tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,<br /> UpdateAccess = CASE WHEN src.UpdateAccess &gt; tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,<br /> DeleteAccess = CASE WHEN src.DeleteAccess &gt; tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,<br /> DBOAccess = CASE WHEN src.DBOAccess &gt; tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,<br /> SysadminAccess = CASE WHEN src.SysadminAccess &gt; tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;<br /> "<br /> $null = $targetCmd.ExecuteNonQuery()<br /> }<br /> elseif ($row["LoginType"] -eq "WINDOWS_LOGIN")<br /> {<br /> $targetCmd.CommandText = "SELECT CacheCount = COUNT(*) FROM dbo.AuditDBUserCache WHERE UserName = '$insertLoginName'"<br /> $queryresults = $targetCmd.ExecuteReader()<br /> $queryresults.Read()<br /> $userCached = $queryresults["CacheCount"]<br /> $queryresults.Close()<br /> $queryresults.Dispose()<br /> <br /> if ($userCached -eq 0)<br /> {<br /> $ADUser = Get-QADUser $insertLoginName<br /> $insertFullName = $ADUser.name<br /> $insertUserName = $ADUser.ntaccountname<br /><br /> $targetCmd.CommandText = "INSERT INTO dbo.AuditDBUserCache (UserName, FullName) VALUES ('$insertLoginName', '$insertFullName')"<br /> $null = $targetCmd.ExecuteNonQuery()<br /> }<br /><br /> $targetCmd.CommandText = "<br /> MERGE dbo.AuditDBLogin AS tgt<br /> USING<br /> (<br /> SELECT<br /> ServerName = '$insertServerName',<br /> Login = UserName,<br /> Username = UserName,<br /> FullName = FullName,<br /> DatabaseName = '$insertDatabaseName',<br /> SelectAccess = $insertSelect,<br /> InsertAccess = $insertInsert,<br /> UpdateAccess = $insertUpdate,<br /> DeleteAccess = $insertDelete,<br /> DBOAccess = $insertDBO,<br /> SysadminAccess = $insertSysadmin<br /> FROM dbo.AuditDBUserCache<br /> WHERE UserName = '$insertUserName'<br /> ) AS src (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> ON <br /> (<br /> tgt.ServerName = src.ServerName<br /> AND tgt.Login = src.Login<br /> AND tgt.Username = src.Username<br /> AND tgt.FullName = src.FullName<br /> AND tgt.DatabaseName = src.DatabaseName<br /> )<br /> WHEN NOT MATCHED THEN<br /> INSERT (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> VALUES (src.ServerName, src.Login, src.Username, src.FullName, src.DatabaseName, src.SelectAccess, src.InsertAccess, src.UpdateAccess, src.DeleteAccess, src.DBOAccess, src.SysadminAccess)<br /> WHEN MATCHED THEN<br /> UPDATE<br /> SET<br /> SelectAccess = CASE WHEN src.SelectAccess &gt; tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,<br /> InsertAccess = CASE WHEN src.InsertAccess &gt; tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,<br /> UpdateAccess = CASE WHEN src.UpdateAccess &gt; tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,<br /> DeleteAccess = CASE WHEN src.DeleteAccess &gt; tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,<br /> DBOAccess = CASE WHEN src.DBOAccess &gt; tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,<br /> SysadminAccess = CASE WHEN src.SysadminAccess &gt; tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;<br /> "<br /> $null = $targetCmd.ExecuteNonQuery()<br /> }<br /> elseif ($row["LoginType"] -eq "WINDOWS_GROUP")<br /> {<br /> $targetCmd.CommandText = "SELECT CacheCount = COUNT(*) FROM dbo.AuditDBGroupCache WHERE Login = '$insertLoginName'"<br /> $queryresults = $targetCmd.ExecuteReader()<br /> $queryresults.Read()<br /> $loginCached = $queryresults["CacheCount"]<br /> $queryresults.Close()<br /> $queryresults.Dispose()<br /><br /> if ($loginCached -eq 0)<br /> {<br /> $groupMembers = Get-QADGroupMember $insertLoginName -Indirect<br /> foreach ($member in $groupMembers)<br /> {<br /> $insertFullName = $member.name<br /> $insertUserName = $member.ntaccountname<br /><br /> $targetCmd.CommandText = "<br /> MERGE dbo.AuditDBUserCache AS tgt<br /> USING<br /> (<br /> SELECT<br /> UserName = '$insertUserName',<br /> FullName = '$insertFullName'<br /> ) AS src (UserName, FullName)<br /> ON tgt.UserName = src.UserName<br /> WHEN NOT MATCHED THEN<br /> INSERT (UserName, FullName)<br /> VALUES (src.UserName, src.FullName);<br /> "<br /><br /> $null = $targetCmd.ExecuteNonQuery()<br /> <br /> $targetCmd.CommandText = "<br /> MERGE dbo.AuditDBGroupCache AS tgt<br /> USING<br /> (<br /> SELECT<br /> Login = '$insertLoginName',<br /> UserName = '$insertUserName'<br /> ) AS src (Login, UserName)<br /> ON tgt.Login = src.Login<br /> AND tgt.UserName = src.UserName<br /> WHEN NOT MATCHED THEN<br /> INSERT (Login, UserName)<br /> VALUES (src.Login, src.UserName);<br /> "<br /> $null = $targetCmd.ExecuteNonQuery()<br /> }<br /> }<br /> $targetCmd.CommandText = "<br /> MERGE dbo.AuditDBLogin AS tgt<br /> USING<br /> (<br /> SELECT<br /> ServerName = '$insertServerName',<br /> Login = AuditDBGroupCache.Login,<br /> Username = AuditDBUserCache.UserName,<br /> FullName = AuditDBUserCache.FullName,<br /> DatabaseName = '$insertDatabaseName',<br /> SelectAccess = $insertSelect,<br /> InsertAccess = $insertInsert,<br /> UpdateAccess = $insertUpdate,<br /> DeleteAccess = $insertDelete,<br /> DBOAccess = $insertDBO,<br /> SysadminAccess = $insertSysadmin<br /> FROM dbo.AuditDBUserCache<br /> INNER JOIN dbo.AuditDBGroupCache<br /> ON AuditDBGroupCache.Username = AuditDBUserCache.Username<br /> WHERE AuditDBGroupCache.Login = '$insertLoginName'<br /> ) AS src (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> ON <br /> (<br /> tgt.ServerName = src.ServerName<br /> AND tgt.Login = src.Login<br /> AND tgt.Username = src.Username<br /> AND tgt.FullName = src.FullName<br /> AND tgt.DatabaseName = src.DatabaseName<br /> )<br /> WHEN NOT MATCHED THEN<br /> INSERT (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> VALUES (src.ServerName, src.Login, src.Username, src.FullName, src.DatabaseName, src.SelectAccess, src.InsertAccess, src.UpdateAccess, src.DeleteAccess, src.DBOAccess, src.SysadminAccess)<br /> WHEN MATCHED THEN<br /> UPDATE<br /> SET<br /> SelectAccess = CASE WHEN src.SelectAccess &gt; tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,<br /> InsertAccess = CASE WHEN src.InsertAccess &gt; tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,<br /> UpdateAccess = CASE WHEN src.UpdateAccess &gt; tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,<br /> DeleteAccess = CASE WHEN src.DeleteAccess &gt; tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,<br /> DBOAccess = CASE WHEN src.DBOAccess &gt; tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,<br /> SysadminAccess = CASE WHEN src.SysadminAccess &gt; tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;<br /> "<br /> $null = $targetCmd.ExecuteNonQuery()<br /> }<br /> }<br /> <br /> $auditConn.Close()<br /> $auditCmd.Dispose()<br /> $auditConn.Dispose() <br /> }<br /> else<br /> {<br /> echo "Database $dbAudit on server $auditServerParam is currently inaccessible"<br /> }<br /> }<br /> }<br /> }<br /><br />$reportConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetServer; Initial Catalog=$targetDBName; Integrated Security=SSPI")<br />$reportConn.Open()<br />$reportCmd = $reportConn.CreateCommand()<br /><br />$reportCmd.CommandText = "<br />SELECT ServerName, UserName, DatabaseName, DescriptionOfChange<br />FROM<br /> (<br /> SELECT<br /> AccessLevel = ROW_NUMBER() OVER(PARTITION BY ServerName, UserName ORDER BY AccessLevel),<br /> ServerName, UserName, DatabaseName, DescriptionOfChange<br /> FROM<br /> (<br /> /* New server logins */<br /> SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'new' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> EXCEPT<br /> SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'new' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> UNION<br /> /* Revoked server logins */<br /> SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> EXCEPT<br /> SELECT AccessLevel = 99, ServerName, UserName, '' AS DatabaseName, 'revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> UNION<br /> /* New read-only access */<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE SelectAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> EXCEPT<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE SelectAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> UNION<br /> /* Revoked read-only access */<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE SelectAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> EXCEPT<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'read revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE SelectAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> UNION<br /> /* New write access */<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> EXCEPT<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> UNION<br /> /* Revoked write access */<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> EXCEPT<br /> SELECT AccessLevel = 3, ServerName, UserName, DatabaseName, 'write revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE InsertAccess = 1 OR UpdateAccess = 1 OR DeleteAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> UNION<br /> /* New DBO access */<br /> SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE DBOAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> EXCEPT<br /> SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE DBOAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> UNION<br /> /* Revoked DBO access */<br /> SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE DBOAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> EXCEPT<br /> SELECT AccessLevel = 2, ServerName, UserName, DatabaseName, 'DBO revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE DBOAccess = 1<br /> AND COALESCE(DatabaseName, '') &lt;&gt; ''<br /> UNION<br /> /* New sysadmin access */<br /> SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE SysadminAccess = 1<br /> EXCEPT<br /> SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN granted' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE SysadminAccess = 1<br /> UNION<br /> /* Revoked sysadmin access */<br /> SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLoginPrevious<br /> WHERE SysadminAccess = 1<br /> EXCEPT<br /> SELECT AccessLevel = 1, ServerName, UserName, '' AS DatabaseName, 'SYSADMIN revoked' AS DescriptionOfChange<br /> FROM dbo.AuditDBLogin<br /> WHERE SysadminAccess = 1<br /> ) AS AllChanges<br /> ) AS RankedChanges<br />WHERE AccessLevel = 1<br /> AND UserName &lt;&gt; ''<br />"<br /><br />$queryresults = $reportCmd.ExecuteReader()<br /><br />$emailbody = ""<br /><br />foreach ($row in $queryresults)<br /> {<br /> $serverName = $row["ServerName"]<br /> $userName = $row["UserName"]<br /> $databaseName = $row["DatabaseName"]<br /> $descriptionOfChange = $row["DescriptionOfChange"]<br /><br /> if ($descriptionOfChange -eq "new") <br /> {<br /> $emailbody = $emailbody + '<span style="color: black;"><b>New login ' + $userName + ' created on server ' + $serverName + '</b></span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "revoked") <br /> {<br /> $emailbody = $emailbody + '<span style="color: black;"><b>Login ' + $userName + ' dropped from server ' + $serverName + '</b></span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "SYSADMIN granted") <br /> {<br /> $emailbody = $emailbody + '<span style="color: red;">' + $userName + ' granted SYSADMIN rights to server ' + $serverName + '</span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "SYSADMIN revoked") <br /> {<br /> $emailbody = $emailbody + '<span style="color: red;">' + $userName + ' SYSADMIN rights to server ' + $serverName + ' revoked</span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "DBO granted") <br /> {<br /> $emailbody = $emailbody + '<span style="color: orange;">' + $userName + ' granted DBO access to database ' + $databaseName + ' on server ' + $serverName + '</span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "DBO revoked") <br /> {<br /> $emailbody = $emailbody + '<span style="color: orange;">' + $userName + ' DBO access to database ' + $databaseName + ' revoked on server ' + $serverName + '</span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "write granted") <br /> {<br /> $emailbody = $emailbody + '<span style="color: yellow;">' + $userName + ' granted WRITE access to database ' + $databaseName + ' on server ' + $serverName + '</span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "write revoked") <br /> {<br /> $emailbody = $emailbody + '<span style="color: yellow;">' + $userName + ' WRITE access to database ' + $databaseName + ' revoked on server ' + $serverName + '</span><br />'<br /> }<br /> elseif ($descriptionOfChange -eq "read revoked")<br /> {<br /> $emailbody = $emailbody + '<span style="color: black;">' + $userName + ' READ access to database ' + $databaseName + ' revoked on server ' + $serverName + '</span><br />'<br /> }<br /> else <br /> {<br /> $emailbody = $emailbody + '<span style="color: black;">' + $userName + ' granted READ access to database ' + $databaseName + ' on server ' + $serverName + '</span><br />'<br /> }<br /> }<br /><br />if ($emailbody)<br /> {<br /> $emailbody = 'The following changes to database access were detected:<br /><br />' + $emailbody<br /> $emailbody += ''<br /><br /> #Send email<br /> $SMTPserver = "mail.pearson.com"<br /> $from = "pearsonvuedba@pearson.com"<br /> $to = "pearsonvuesqldba@pearson.com"<br /> $subject = "Database Access Changes Detected"<br /><br /> $mailer = new-object Net.Mail.SMTPclient($SMTPserver)<br /> $msg = new-object Net.Mail.MailMessage($from, $to, $subject, $emailbody)<br /> $msg.IsBodyHTML = $true<br /> $mailer.send($msg)<br /><br /> $msg.Dispose()<br /> }<br /></pre><br /></div><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-8111150960000091329?l=www.real-sql-guy.com' alt='' /></div>Tracy McKibbenhttp://www.blogger.com/profile/18326025369398096349noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-23453666372258255352011-09-22T08:28:00.000-07:002011-09-22T08:28:34.347-07:00Automated Permissions Auditing With Powershell and T-SQL: Part 5<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/-niYDNZDRZo0/Tm4kqMrG21I/AAAAAAAAH-k/lYXkMQexArg/s1600/spy.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-niYDNZDRZo0/Tm4kqMrG21I/AAAAAAAAH-k/lYXkMQexArg/s1600/spy.jpg" /></a></div>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.<br /><br />By now, you should be starting to appreciate how much power (pun intended) Powershell gives you. &nbsp;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. &nbsp;That's what we're going to look at in this segment.<br /><br />First, let's recap:<br /><br /><ul><li>in <a href="http://www.real-sql-guy.com/2011/09/automated-permissions-auditing-with.html">Part 1</a>, I gave you a SQL script to dump SQL Server 2005/2008 permissions</li><li>in <a href="http://www.real-sql-guy.com/2011/09/automated-permissions-auditing-with_14.html">Part 2</a>, I gave you the same script for SQL 2000</li><li>in <a href="http://www.real-sql-guy.com/2011/09/automated-permissions-auditing-with_15.html">Part 3</a>, I showed you how to wrap Powershell around those queries, in order to dump permissions from all databases</li><li>in <a href="http://www.real-sql-guy.com/2011/09/previously-on-real-sql-guy.html">Part 4</a>, I showed you to save those permissions to a table</li></ul><br />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. &nbsp;Some of those database logins may be Active Directory domain groups, with multiple users belonging to them. &nbsp;We can't see that from inside SQL Server, so if an auditor asks "Who's in this group?", we can't tell them. &nbsp;With the <a href="http://www.quest.com/powershell/activeroles-server.aspx">Free Powershell Commands for Active Directory</a>, a free "CmdLet" (an extension for Powershell) from Quest, we can.<br /><br />With the CmdLet installed, this simple 2-line script will list the members of an Active Directory group:<br /><br /><pre class="brush: powershell"><br />Add-PSSnapin Quest.ActiveRoles.ADManagement<br />Get-QADGroupMember RealSQLAD\RealSQLGroup<br /></pre><br />Doesn't get any easier than that. &nbsp;The real magic is yet to come, however, as we integrate that functionality into our audit script. &nbsp;If you were paying attention during the last segment, you'll recall that I pointed out the "FullName" column. &nbsp;If you've been paying even closer attention, you've noticed that the T-SQL statements from <a href="http://www.real-sql-guy.com/2011/09/automated-permissions-auditing-with.html">Part 1</a> and <a href="http://www.real-sql-guy.com/2011/09/automated-permissions-auditing-with_14.html">Part 2</a> return a column named "LoginType". &nbsp;Today we're going to find out why those columns exist.<br /><br />With all of this data available to us, there's no reason not to provide some sort of report, makes the management types happy. &nbsp;Those same management types tend to prefer human names (who is "AD\miljk1"?). &nbsp;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:<br /><br /><pre class="brush: powershell"><br />if ($row["LoginType"] -eq "WINDOWS_LOGIN")<br /> {<br /> $ADUser = Get-QADUser $insertLoginName<br /> $insertFullName = $ADUser.name<br /> $insertUserName = $ADUser.ntaccountname<br /> }<br /></pre><br />That takes care of individual Windows users, but what about groups? &nbsp;Almost as easy:<br /><br /><pre class="brush: powershell"><br />if ($row["LoginType"] -eq "WINDOWS_GROUP")<br /> {<br /> $groupMembers = Get-QADGroupMember $insertLoginName -Indirect<br /> foreach ($member in $groupMembers)<br /> {<br /> $insertFullName = $member.name<br /> $insertUserName = $member.ntaccountname<br /> }<br /> }<br /></pre><br />Put all of this together with our existing script, and here's what we have:<br /><br /><pre class="brush: powershell"><br /># Specify server to audit<br /># Note the non-standard port number of 50000. This can be omitted if SQL Server is listening on standard port 1433<br />$auditServerName = "REALSQLGUYSERVER,50000"<br /> <br /># Create connection object to SQL Server instance<br />[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null<br />$auditSMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist "$auditServerName"<br /> <br /># Specify server to audit<br />$targetServerName = "TARGETSERVER"<br />$targetDBName = "AuditDB"<br /> <br />$targetConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetServerName; Initial Catalog=$targetDBName; Integrated Security=SSPI")<br />$targetConn.Open()<br />$targetCmd = $targetConn.CreateCommand()<br /><br /># Include Quest Active Directory CmdLet<br />Add-PSSnapin Quest.ActiveRoles.ADManagement<br /> <br /># Create AuditDBLogin table if it doesn't exist<br />$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));"<br />$null = $targetCmd.ExecuteNonQuery()<br /> <br /># SQL Server is 2000<br />if ($auditSMOServer.Information.VersionMajor -eq 8)<br /> {<br /> $UserDumpQueryText = "<br />SELECT<br /> ServerName = @@SERVERNAME,<br /> LoginName = AccessSummary.LoginName,<br /> LoginType = CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,<br /> DatabaseName = DB_NAME(),<br /> SelectAccess = MAX(AccessSummary.SelectAccess),<br /> InsertAccess = MAX(AccessSummary.InsertAccess),<br /> UpdateAccess = MAX(AccessSummary.UpdateAccess),<br /> DeleteAccess = MAX(AccessSummary.DeleteAccess),<br /> DBOAccess = MAX(AccessSummary.DBOAccess),<br /> SysadminAccess = MAX(AccessSummary.SysadminAccess)<br />FROM<br /> (<br /> /* Get logins with permissions */<br /> SELECT <br /> LoginName = sysusers.name,<br /> SelectAccess = CASE WHEN sysprotects.action = 193 THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN sysprotects.action = 195 THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN sysprotects.action = 196 THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN sysprotects.action = 197 THEN 1 ELSE 0 END,<br /> DBOAccess = 0,<br /> SysadminAccess = 0<br /> FROM dbo.sysobjects<br /> INNER JOIN dbo.sysprotects<br /> ON sysprotects.id = sysobjects.id<br /> INNER JOIN dbo.sysusers<br /> ON sysusers.uid = sysprotects.uid<br /> INNER JOIN master.dbo.syslogins AS syslogins<br /> ON syslogins.sid = sysusers.sid<br /> WHERE sysobjects.type IN ('U', 'V')<br /> AND sysusers.issqlrole = 0<br /> AND sysprotects.protecttype IN (204, 205)<br /> AND sysprotects.action IN (193, 195, 196, 197)<br /> UNION ALL<br /> /* Get group members with permissions */<br /> SELECT <br /> LoginName = sysusersMember.name,<br /> SelectAccess = CASE WHEN sysprotects.action = 193 THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN sysprotects.action = 195 THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN sysprotects.action = 196 THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN sysprotects.action = 197 THEN 1 ELSE 0 END,<br /> DBOAccess = 0,<br /> SysadminAccess = 0<br /> FROM dbo.sysobjects<br /> INNER JOIN dbo.sysprotects<br /> ON sysprotects.id = sysobjects.id<br /> INNER JOIN dbo.sysusers AS sysusersRole<br /> ON sysusersRole.uid = sysprotects.uid<br /> INNER JOIN dbo.sysmembers<br /> ON sysmembers.groupuid = sysusersRole.uid<br /> INNER JOIN dbo.sysusers AS sysusersMember<br /> ON sysusersMember.uid = sysmembers.memberuid<br /> INNER JOIN master.dbo.syslogins AS syslogins<br /> ON syslogins.sid = sysusersMember.sid<br /> WHERE sysobjects.type IN ('U', 'V')<br /> AND sysusersRole.issqlrole = 1<br /> AND sysusersRole.name NOT IN ('public')<br /> AND sysprotects.protecttype IN (204, 205)<br /> AND sysprotects.action IN (193, 195, 196, 197)<br /> UNION ALL<br /> /* Get users in db_owner, db_datareader and db_datawriter */<br /> SELECT<br /> LoginName = syslogins.name,<br /> SelectAccess = CASE WHEN sysusers.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> DBOAccess = CASE WHEN sysusers.name IN ('db_owner') THEN 1 ELSE 0 END,<br /> SysadminAccess = 0<br /> FROM dbo.sysusers<br /> INNER JOIN dbo.sysmembers<br /> ON sysmembers.groupuid = sysusers.uid<br /> INNER JOIN dbo.sysusers AS sysusersMember<br /> ON sysusersMember.uid = sysmembers.memberuid<br /> INNER JOIN master.dbo.syslogins AS syslogins<br /> ON syslogins.sid = sysusersMember.sid<br /> WHERE sysusers.name IN ('db_owner', 'db_datareader', 'db_datawriter')<br /> UNION ALL<br /> /* Get users in sysadmin */<br /> SELECT<br /> LoginName = syslogins.name,<br /> SelectAccess = 1,<br /> InsertAccess = 1,<br /> UpdateAccess = 1,<br /> DeleteAccess = 1,<br /> DBOAccess = 0,<br /> SysadminAccess = 1<br /> FROM master.dbo.syslogins AS syslogins<br /> WHERE syslogins.sysadmin = 1<br /> ) AS AccessSummary<br />INNER JOIN master.dbo.syslogins AS syslogins<br /> ON syslogins.loginname = AccessSummary.LoginName<br />WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT')<br />GROUP BY<br /> AccessSummary.LoginName,<br /> CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END<br /> "<br /> }<br /># SQL Server is 2005 or greater<br />elseif ($auditSMOServer.Information.VersionMajor -gt 8)<br /> {<br /> $UserDumpQueryText = "<br />SELECT<br /> ServerName = @@SERVERNAME,<br /> LoginName = AccessSummary.LoginName,<br /> LoginType = CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,<br /> DatabaseName = DB_NAME(),<br /> SelectAccess = MAX(AccessSummary.SelectAccess),<br /> InsertAccess = MAX(AccessSummary.InsertAccess),<br /> UpdateAccess = MAX(AccessSummary.UpdateAccess),<br /> DeleteAccess = MAX(AccessSummary.DeleteAccess),<br /> DBOAccess = MAX(AccessSummary.DBOAccess),<br /> SysadminAccess = MAX(AccessSummary.SysadminAccess)<br />FROM<br /> (<br /> /* Get logins with permissions */<br /> SELECT <br /> LoginName = sysDatabasePrincipal.name,<br /> SelectAccess = CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,<br /> DBOAccess = 0,<br /> SysadminAccess = 0<br /> FROM sys.database_permissions AS sysDatabasePermission<br /> INNER JOIN sys.database_principals AS sysDatabasePrincipal<br /> ON sysDatabasePrincipal.principal_id = sysDatabasePermission.grantee_principal_id<br /> INNER JOIN sys.server_principals AS sysServerPrincipal<br /> ON sysServerPrincipal.sid = sysDatabasePrincipal.sid<br /> WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'<br /> AND sysDatabasePrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')<br /> AND sysServerPrincipal.is_disabled = 0<br /> UNION ALL<br /> /* Get group members with permissions */<br /> SELECT <br /> LoginName = sysDatabasePrincipalMember.name,<br /> SelectAccess = CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,<br /> DBOAccess = 0,<br /> SysadminAccess = 0<br /> FROM sys.database_permissions AS sysDatabasePermission<br /> INNER JOIN sys.database_principals AS sysDatabasePrincipalRole<br /> ON sysDatabasePrincipalRole.principal_id = sysDatabasePermission.grantee_principal_id<br /> INNER JOIN sys.database_role_members AS sysDatabaseRoleMember<br /> ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id<br /> INNER JOIN sys.database_principals AS sysDatabasePrincipalMember<br /> ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id<br /> INNER JOIN sys.server_principals AS sysServerPrincipal<br /> ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid<br /> WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'<br /> AND sysDatabasePrincipalRole.type_desc = 'DATABASE_ROLE'<br /> AND sysDatabasePrincipalRole.name <> 'public'<br /> AND sysDatabasePrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')<br /> AND sysServerPrincipal.is_disabled = 0<br /> UNION ALL<br /> /* Get users in db_owner, db_datareader and db_datawriter */<br /> SELECT<br /> LoginName = sysServerPrincipal.name,<br /> SelectAccess = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> DBOAccess = CASE WHEN sysDatabasePrincipalRole.name = 'db_owner' THEN 1 ELSE 0 END,<br /> SysadminAccess = 0<br /> FROM sys.database_principals AS sysDatabasePrincipalRole<br /> INNER JOIN sys.database_role_members AS sysDatabaseRoleMember<br /> ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id<br /> INNER JOIN sys.database_principals AS sysDatabasePrincipalMember<br /> ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id<br /> INNER JOIN sys.server_principals AS sysServerPrincipal<br /> ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid<br /> WHERE sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader', 'db_datawriter')<br /> AND sysServerPrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')<br /> AND sysServerPrincipal.is_disabled = 0<br /> UNION ALL<br /> /* Get users in sysadmin */<br /> SELECT<br /> LoginName = sysServerPrincipalMember.name,<br /> SelectAccess = 1,<br /> InsertAccess = 1,<br /> UpdateAccess = 1,<br /> DeleteAccess = 1,<br /> DBOAccess = 0,<br /> SysadminAccess = 1<br /> FROM sys.server_principals AS sysServerPrincipalRole<br /> INNER JOIN sys.server_role_members AS sysServerRoleMember<br /> ON sysServerRoleMember.role_principal_id = sysServerPrincipalRole.principal_id<br /> INNER JOIN sys.server_principals AS sysServerPrincipalMember<br /> ON sysServerPrincipalMember.principal_id = sysServerRoleMember.member_principal_id<br /> WHERE sysServerPrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')<br /> AND sysServerPrincipalMember.is_disabled = 0<br /> ) AS AccessSummary<br />INNER JOIN master.dbo.syslogins AS syslogins<br /> ON syslogins.loginname = AccessSummary.LoginName<br />WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT')<br />GROUP BY<br /> AccessSummary.LoginName,<br /> CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END<br /> "<br /> }<br /># Unknown or unexpected SQL Server version<br />else<br /> {<br /> $UserDumpQueryText = "Unsupported SQL Server version"<br /> }<br /> <br /># Version is good, let's proceed<br />if ($UserDumpQueryText -ne "Unsupported SQL Server version")<br /> {<br /> # Loop through each database on the SQL instance<br /> foreach ($dbAudit in $auditSMOserver.databases)<br /> {<br /> if ($dbAudit.IsAccessible)<br /> {<br /> # Connect to the database<br /> $auditDBName = $dbAudit.Name<br /> $auditConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$auditServerName; Initial Catalog=$auditDBName; Integrated Security=SSPI")<br /> $auditConn.Open()<br /> $auditCmd = $auditConn.CreateCommand()<br /> <br /> # Run the permissions dump query against the current database<br /> $auditCmd.CommandText = $UserDumpQueryText<br /> $queryresults = $auditCmd.ExecuteReader()<br /> <br /> # Loop through permission dump resultset, print each value<br /> foreach ($row in $queryresults)<br /> {<br /> $insertServerName = $row["ServerName"]<br /> $insertDatabaseName = $row["DatabaseName"]<br /> $insertLoginName = $row["LoginName"]<br /> $insertUserName = $row["LoginName"]<br /> $insertFullName = ""<br /> $insertSelect = $row["SelectAccess"]<br /> $insertInsert = $row["InsertAccess"]<br /> $insertUpdate = $row["UpdateAccess"]<br /> $insertDelete = $row["DeleteAccess"]<br /> $insertDBO = $row["DBOAccess"]<br /> $insertSysadmin = $row["SysadminAccess"]<br /><br /> if ($row["LoginType"] -eq "SQL_USER")<br /> {<br /> $targetCmd.CommandText = "<br />MERGE dbo.AuditDBLogin AS tgt<br />USING<br /> (<br /> SELECT<br /> ServerName = '$insertServerName',<br /> Login = '$insertLoginName',<br /> Username = '$insertUsername',<br /> FullName = '$insertFullName',<br /> DatabaseName = '$insertDatabaseName',<br /> SelectAccess = $insertSelect,<br /> InsertAccess = $insertInsert,<br /> UpdateAccess = $insertUpdate,<br /> DeleteAccess = $insertDelete,<br /> DBOAccess = $insertDBO,<br /> SysadminAccess = $insertSysadmin<br /> ) AS src (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> ON <br /> (<br /> tgt.ServerName = src.ServerName<br /> AND tgt.Login = src.Login<br /> AND tgt.Username = src.Username<br /> AND tgt.FullName = src.FullName<br /> AND tgt.DatabaseName = src.DatabaseName<br /> )<br /> WHEN NOT MATCHED THEN<br /> INSERT (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> VALUES (src.ServerName, src.Login, src.Username, src.FullName, src.DatabaseName, src.SelectAccess, src.InsertAccess, src.UpdateAccess, src.DeleteAccess, src.DBOAccess, src.SysadminAccess)<br /> WHEN MATCHED THEN<br /> UPDATE<br /> SET<br /> SelectAccess = CASE WHEN src.SelectAccess > tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,<br /> InsertAccess = CASE WHEN src.InsertAccess > tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,<br /> UpdateAccess = CASE WHEN src.UpdateAccess > tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,<br /> DeleteAccess = CASE WHEN src.DeleteAccess > tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,<br /> DBOAccess = CASE WHEN src.DBOAccess > tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,<br /> SysadminAccess = CASE WHEN src.SysadminAccess > tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;<br /> "<br /> $null = $targetCmd.ExecuteNonQuery()<br /> }<br /> elseif ($row["LoginType"] -eq "WINDOWS_LOGIN")<br /> {<br /> $ADUser = Get-QADUser $insertLoginName<br /> $insertFullName = $ADUser.name<br /> $insertUserName = $ADUser.ntaccountname<br /><br /> $targetCmd.CommandText = "<br />MERGE dbo.AuditDBLogin AS tgt<br />USING<br /> (<br /> SELECT<br /> ServerName = '$insertServerName',<br /> Login = '$insertLoginName',<br /> Username = '$insertUsername',<br /> FullName = '$insertFullName',<br /> DatabaseName = '$insertDatabaseName',<br /> SelectAccess = $insertSelect,<br /> InsertAccess = $insertInsert,<br /> UpdateAccess = $insertUpdate,<br /> DeleteAccess = $insertDelete,<br /> DBOAccess = $insertDBO,<br /> SysadminAccess = $insertSysadmin<br /> ) AS src (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> ON <br /> (<br /> tgt.ServerName = src.ServerName<br /> AND tgt.Login = src.Login<br /> AND tgt.Username = src.Username<br /> AND tgt.FullName = src.FullName<br /> AND tgt.DatabaseName = src.DatabaseName<br /> )<br /> WHEN NOT MATCHED THEN<br /> INSERT (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> VALUES (src.ServerName, src.Login, src.Username, src.FullName, src.DatabaseName, src.SelectAccess, src.InsertAccess, src.UpdateAccess, src.DeleteAccess, src.DBOAccess, src.SysadminAccess)<br /> WHEN MATCHED THEN<br /> UPDATE<br /> SET<br /> SelectAccess = CASE WHEN src.SelectAccess > tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,<br /> InsertAccess = CASE WHEN src.InsertAccess > tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,<br /> UpdateAccess = CASE WHEN src.UpdateAccess > tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,<br /> DeleteAccess = CASE WHEN src.DeleteAccess > tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,<br /> DBOAccess = CASE WHEN src.DBOAccess > tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,<br /> SysadminAccess = CASE WHEN src.SysadminAccess > tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;<br /> "<br /> $null = $targetCmd.ExecuteNonQuery()<br /> }<br /> elseif ($row["LoginType"] -eq "WINDOWS_GROUP")<br /> {<br /> $groupMembers = Get-QADGroupMember $insertLoginName -Indirect<br /> foreach ($member in $groupMembers)<br /> {<br /> $insertFullName = $member.name<br /> $insertUserName = $member.ntaccountname<br /> <br /> $targetCmd.CommandText = "<br />MERGE dbo.AuditDBLogin AS tgt<br />USING<br /> (<br /> SELECT<br /> ServerName = '$insertServerName',<br /> Login = '$insertLoginName',<br /> Username = '$insertUsername',<br /> FullName = '$insertFullName',<br /> DatabaseName = '$insertDatabaseName',<br /> SelectAccess = $insertSelect,<br /> InsertAccess = $insertInsert,<br /> UpdateAccess = $insertUpdate,<br /> DeleteAccess = $insertDelete,<br /> DBOAccess = $insertDBO,<br /> SysadminAccess = $insertSysadmin<br /> ) AS src (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> ON <br /> (<br /> tgt.ServerName = src.ServerName<br /> AND tgt.Login = src.Login<br /> AND tgt.Username = src.Username<br /> AND tgt.FullName = src.FullName<br /> AND tgt.DatabaseName = src.DatabaseName<br /> )<br /> WHEN NOT MATCHED THEN<br /> INSERT (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> VALUES (src.ServerName, src.Login, src.Username, src.FullName, src.DatabaseName, src.SelectAccess, src.InsertAccess, src.UpdateAccess, src.DeleteAccess, src.DBOAccess, src.SysadminAccess)<br /> WHEN MATCHED THEN<br /> UPDATE<br /> SET<br /> SelectAccess = CASE WHEN src.SelectAccess > tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,<br /> InsertAccess = CASE WHEN src.InsertAccess > tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,<br /> UpdateAccess = CASE WHEN src.UpdateAccess > tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,<br /> DeleteAccess = CASE WHEN src.DeleteAccess > tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,<br /> DBOAccess = CASE WHEN src.DBOAccess > tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,<br /> SysadminAccess = CASE WHEN src.SysadminAccess > tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;<br /> "<br /> $null = $targetCmd.ExecuteNonQuery()<br /><br /> }<br /> }<br /> }<br /> }<br /> }<br /> }<br /></pre><br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-2345366637225825535?l=www.real-sql-guy.com' alt='' /></div>Tracy McKibbenhttp://www.blogger.com/profile/18326025369398096349noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-91265553055946229862011-09-19T12:31:00.000-07:002011-09-19T12:31:47.105-07:00THE Interview Question<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-I0OiPj2dmSI/TneUoLbm0lI/AAAAAAAAABM/tjxWmFd2G8o/s1600/Question_Clip_Art.gif" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="200" src="http://1.bp.blogspot.com/-I0OiPj2dmSI/TneUoLbm0lI/AAAAAAAAABM/tjxWmFd2G8o/s200/Question_Clip_Art.gif" width="115" /></a></div>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. &nbsp;The "Help Wanted" sign was placed in the front window of the Guy-cave, and the interviews commenced.<br /><br />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. &nbsp;"Can you spell S-Q-L? &nbsp;No? &nbsp;Door's over there."<br /><br />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.<br /><br />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".<br /><br />"You're the on-call DBA, supporting, among other things, a 200-seat call center. &nbsp;Each machine in that call center is running an application that reads and writes to your SQL Server database. &nbsp;It's Friday afternoon, and you suddenly start getting calls from the Call Center users, each claiming that their application has hung. &nbsp;There is no error message, the application has simply frozen. &nbsp;What do you do?"<br /><br />Some of the actual responses that were given to this question:<br /><br />- use the Index Tuning Wizard to see if there's a missing index<br />- check to see if SQL Server has consumed all of the memory on the server, because it will do that sometimes<br />- have someone check each workstation to see if something is pegging the CPU<br />- use Profiler to record the server activity<br />- that's something our IT staff would handle<br /><br />Of the dozen or more candidates that were asked this question, only two gave me the answer I was looking for: &nbsp;sp_who2<br /><br />In my opinion, under these circumstances, there is only one right answer. &nbsp;Run sp_who2, look for a blocker, and kill that SPID. &nbsp;That's the only reasonable solution when you have 200 call center agents, on the phone with customers, waiting for a resolution.<br /><br />How would you answer the question?<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-9126555305594622986?l=www.real-sql-guy.com' alt='' /></div>REAL SQL Guyhttp://www.blogger.com/profile/07486104775304135831noreply@blogger.com3tag:blogger.com,1999:blog-2146276824340207020.post-58686641992205520622011-09-19T09:01:00.000-07:002011-09-19T09:02:29.250-07:00Automated Permissions Auditing With Powershell and T-SQL: Part 4<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/-niYDNZDRZo0/Tm4kqMrG21I/AAAAAAAAH-k/lYXkMQexArg/s1600/spy.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-niYDNZDRZo0/Tm4kqMrG21I/AAAAAAAAH-k/lYXkMQexArg/s1600/spy.jpg" /></a></div>Previously, on REAL-SQL-Guy...<br /><br />We were introduced to two SQL queries to obtain database login permissions, one for <a href="http://www.real-sql-guy.com/2011/09/automated-permissions-auditing-with_14.html">SQL Server 2000</a>, one for <a href="http://www.real-sql-guy.com/2011/09/automated-permissions-auditing-with.html">SQL Server 2005</a> and higher.<br /><br />We saw how these queries, <a href="http://www.real-sql-guy.com/2011/09/automated-permissions-auditing-with_15.html">combined with Powershell</a>, can be used to loop through all databases on a SQL Server instance to collect permissions. &nbsp;Cool stuff, but kind of useless unless we save that information someplace and actually make use of it. &nbsp;Let's expand the process a bit, to include writing the collected permissions data to a table.<br /><br />First, we need to create a table, if one doesn't already exist. &nbsp;In order to use the script that I'm presenting here, the table MUST be placed on a SQL 2008 instance.<br /><br /><pre class="brush: powershell"><br /># Create AuditDBLogin table if it doesn't exist<br />$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));"<br />$null = $targetCmd.ExecuteNonQuery()<br /></pre><br />Notice the column named FullName - remember this column, more on that later!<br /><br />The reason for placing the collection table on a SQL 2008 instance is the use of the MERGE statement. &nbsp;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.<br /><br />The evolving Powershell script now looks like this:<br /><br /><pre class="brush: powershell"><br /># Specify server to audit<br /># Note the non-standard port number of 50000. This can be omitted if SQL Server is listening on standard port 1433<br />$auditServerName = "REALSQLGUYSERVER,50000"<br /> <br /># Create connection object to SQL Server instance<br />[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null<br />$auditSMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist "$auditServerName"<br /><br /># Specify server to audit<br />$targetServerName = "TARGETSERVER"<br />$targetDBName = "AuditDB"<br /><br />$targetConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$targetServerName; Initial Catalog=$targetDBName; Integrated Security=SSPI")<br />$targetConn.Open()<br />$targetCmd = $targetConn.CreateCommand()<br /><br /># Create AuditDBLogin table if it doesn't exist<br />$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));"<br />$null = $targetCmd.ExecuteNonQuery()<br /> <br /># SQL Server is 2000<br />if ($auditSMOServer.Information.VersionMajor -eq 8)<br /> {<br /> $UserDumpQueryText = "query from part 2"<br /> }<br /># SQL Server is 2005 or greater<br />elseif ($auditSMOServer.Information.VersionMajor -gt 8)<br /> {<br /> $UserDumpQueryText = "query from part 1"<br /> }<br /># Unknown or unexpected SQL Server version<br />else<br /> {<br /> $UserDumpQueryText = "Unsupported SQL Server version"<br /> }<br /> <br /># Version is good, let's proceed<br />if ($UserDumpQueryText -ne "Unsupported SQL Server version")<br /> {<br /> # Loop through each database on the SQL instance<br /> foreach ($dbAudit in $auditSMOserver.databases)<br /> {<br /> if ($dbAudit.IsAccessible)<br /> {<br /> # Connect to the database<br /> $auditDBName = $dbAudit.Name<br /> $auditConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$auditServerName; Initial Catalog=$auditDBName; Integrated Security=SSPI")<br /> $auditConn.Open()<br /> $auditCmd = $auditConn.CreateCommand()<br /> <br /> # Run the permissions dump query against the current database<br /> $auditCmd.CommandText = $UserDumpQueryText<br /> $queryresults = $auditCmd.ExecuteReader()<br /> <br /> # Loop through permission dump resultset, print each value<br /> foreach ($row in $queryresults)<br /> {<br /> $insertServerName = $row["ServerName"]<br /> $insertDatabaseName = $row["DatabaseName"]<br /> $insertLoginName = $row["LoginName"]<br /> $insertUserName = $row["LoginName"]<br /> $insertFullName = ""<br /> $insertSelect = $row["SelectAccess"]<br /> $insertInsert = $row["InsertAccess"]<br /> $insertUpdate = $row["UpdateAccess"]<br /> $insertDelete = $row["DeleteAccess"]<br /> $insertDBO = $row["DBOAccess"]<br /> $insertSysadmin = $row["SysadminAccess"]<br /><br /> $targetCmd.CommandText = "<br />MERGE dbo.AuditDBLogin AS tgt<br />USING<br /> (<br /> SELECT<br /> ServerName = '$insertServerName',<br /> Login = '$insertLoginName',<br /> Username = '$insertUsername',<br /> FullName = '$insertFullName',<br /> DatabaseName = '$insertDatabaseName',<br /> SelectAccess = $insertSelect,<br /> InsertAccess = $insertInsert,<br /> UpdateAccess = $insertUpdate,<br /> DeleteAccess = $insertDelete,<br /> DBOAccess = $insertDBO,<br /> SysadminAccess = $insertSysadmin<br /> ) AS src (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> ON <br /> (<br /> tgt.ServerName = src.ServerName<br /> AND tgt.Login = src.Login<br /> AND tgt.Username = src.Username<br /> AND tgt.FullName = src.FullName<br /> AND tgt.DatabaseName = src.DatabaseName<br /> )<br /> WHEN NOT MATCHED THEN<br /> INSERT (ServerName, Login, Username, FullName, DatabaseName, SelectAccess, InsertAccess, UpdateAccess, DeleteAccess, DBOAccess, SysadminAccess)<br /> VALUES (src.ServerName, src.Login, src.Username, src.FullName, src.DatabaseName, src.SelectAccess, src.InsertAccess, src.UpdateAccess, src.DeleteAccess, src.DBOAccess, src.SysadminAccess)<br /> WHEN MATCHED THEN<br /> UPDATE<br /> SET<br /> SelectAccess = CASE WHEN src.SelectAccess > tgt.SelectAccess THEN src.SelectAccess ELSE tgt.SelectAccess END,<br /> InsertAccess = CASE WHEN src.InsertAccess > tgt.InsertAccess THEN src.InsertAccess ELSE tgt.InsertAccess END,<br /> UpdateAccess = CASE WHEN src.UpdateAccess > tgt.UpdateAccess THEN src.UpdateAccess ELSE tgt.UpdateAccess END,<br /> DeleteAccess = CASE WHEN src.DeleteAccess > tgt.DeleteAccess THEN src.DeleteAccess ELSE tgt.DeleteAccess END,<br /> DBOAccess = CASE WHEN src.DBOAccess > tgt.DBOAccess THEN src.DBOAccess ELSE tgt.DBOAccess END,<br /> SysadminAccess = CASE WHEN src.SysadminAccess > tgt.SysadminAccess THEN src.SysadminAccess ELSE tgt.SysadminAccess END;<br /> "<br /> $null = $targetCmd.ExecuteNonQuery()<br /> }<br /> }<br /> }<br /> }<br /></pre><br /><br /><br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-5868664199220552062?l=www.real-sql-guy.com' alt='' /></div>Tracy McKibbenhttp://www.blogger.com/profile/18326025369398096349noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-56571597424406512011-09-16T11:01:00.000-07:002011-09-16T11:02:50.462-07:00Rasslin' With a Reluctant Restore<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-X3bOuj3ZAkU/TnOHKstiXoI/AAAAAAAAABA/hbRD_R9dQK0/s1600/wtf.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="140" src="http://2.bp.blogspot.com/-X3bOuj3ZAkU/TnOHKstiXoI/AAAAAAAAABA/hbRD_R9dQK0/s200/wtf.jpg" width="200" /></a></div>It's Friday, a day that I like to sit and&nbsp;reminisce (OK, stare out the window daydreaming). &nbsp;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.<br /><br />All was right with the world, the universe was in order, and I think I was doing something, possibly staring out the window. &nbsp;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. &nbsp;Faster than a speeding table scan, I spun around in my chair and grabbed my keyboard.<br /><br />I began my interrogation of the victim - "What's the error message?"<br />"There isn't one", he replied, "The job just sits there and doesn't do anything!"<br /><br />So much for advanced troubleshooting skills. &nbsp;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. &nbsp;This other process was stuck in a KILLED/ROLLBACK state, and belonged to none other than the DBA sitting beside me.<br /><br /><div class="separator" style="clear: both; text-align: left;"><a href="http://3.bp.blogspot.com/-pqXYMfzzVDk/TnON4K_Qi3I/AAAAAAAAABI/nkTNRsXRafk/s1600/stuckspid.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-pqXYMfzzVDk/TnON4K_Qi3I/AAAAAAAAABI/nkTNRsXRafk/s640/stuckspid.jpg" width="620" /></a></div><br />"What is this?", I asked. &nbsp;He replied "Oh, that's something I killed earlier in the week, it shouldn't be there". &nbsp;Well, it is, so let's figure out why.<br /><br />Pulling DBCC INPUTBUFFER out of my, umm, utility belt, I found that this shouldn't-exist-but-does process had been running xp_cmdshell:<br /><br /><pre class="brush: sql">xp_cmdshell '\\ServerA\Import\SampleLiveData_ClientQuestions.txt'<br /></pre><br />Again I asked, "What is this?"<br />"Oh, I was trying to import a text file, I thought that might open it up within Management Studio" was the response.<br /><br />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." &nbsp;To prove my point, I open up Windows Task Manager on the server:<br /><br /><div class="separator" style="clear: both; text-align: left;"><a href="http://2.bp.blogspot.com/-hZi8Cy7Jx0E/TnOLmcON1GI/AAAAAAAAABE/kua1E_uvWTw/s1600/notepad.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-hZi8Cy7Jx0E/TnOLmcON1GI/AAAAAAAAABE/kua1E_uvWTw/s640/notepad.jpg" width="620" /></a></div><br />I then further drove the point home by clicking the "End Process" button, dispatching NOTEPAD.EXE into the bit bucket. &nbsp;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. &nbsp;More importantly, the RESTORE DATABASE process was no longer blocked, and was in fact actively generating disk I/O. &nbsp;Less than an hour later, the restore was complete, and the universe was once again in order.<br /><br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-5657159742440651?l=www.real-sql-guy.com' alt='' /></div>REAL SQL Guyhttp://www.blogger.com/profile/07486104775304135831noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-7452451495268983872011-09-15T07:17:00.000-07:002011-09-15T08:06:56.280-07:00Automated Permissions Auditing With Powershell and T-SQL: Part 3<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/-niYDNZDRZo0/Tm4kqMrG21I/AAAAAAAAH-k/lYXkMQexArg/s1600/spy.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-niYDNZDRZo0/Tm4kqMrG21I/AAAAAAAAH-k/lYXkMQexArg/s1600/spy.jpg" /></a></div>In this episode, I'm going to start introducing some of the Powershell elements that tie this audit process together.<br /><br /><i><b>DISCLAIMER: &nbsp;I am NOT a Powershell expert, I have only recently begun to use it. &nbsp;There are likely parts of this process that can be done differently, more efficiently, or are just plain wrong. &nbsp;If there are Powershell gurus in the house, please comment, let's make this a two-way educational process.</b></i><br /><br />Whew, that's out of the way, so let's move on.<br /><br />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. &nbsp;In this, part 3, I will show you the beginnings of a Powershell script that will utilize those two queries.<br /><br />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. &nbsp;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. &nbsp;Baby steps...<br /><br /><pre class="brush: powershell"><br /># Specify server to audit<br /># Note the non-standard port number of 50000. This can be omitted if SQL Server is listening on standard port 1433<br />$auditServerName = "REALSQLGUYSERVER,50000"<br /><br /># Create connection object to SQL Server instance<br /># Note the non-standard port number of 50000. This can be omitted if SQL Server is listening on standard port 1433<br />[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null<br />$auditSMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist "$auditServerName"<br /><br /># SQL Server is 2000<br />if ($auditSMOServer.Information.VersionMajor -eq 8)<br /> {<br /> $UserDumpQueryText = "query from part 2"<br /> }<br /># SQL Server is 2005 or greater<br />elseif ($auditSMOServer.Information.VersionMajor -gt 8)<br /> {<br /> $UserDumpQueryText = "query from part 1"<br /> }<br /># Unknown or unexpected SQL Server version<br />else<br /> {<br /> $UserDumpQueryText = "Unsupported SQL Server version"<br /> }<br /><br /># Version is good, let's proceed<br />if ($UserDumpQueryText -ne "Unsupported SQL Server version")<br /> {<br /> # Loop through each database on the SQL instance<br /> foreach ($dbAudit in $auditSMOserver.databases)<br /> {<br /> if ($dbAudit.IsAccessible)<br /> {<br /> # Connect to the database<br /> $auditDBName = $dbAudit.Name<br /> $auditConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$auditServerName; Initial Catalog=$auditDBName; Integrated Security=SSPI")<br /> $auditConn.Open()<br /> $auditCmd = $auditConn.CreateCommand()<br /><br /> # Run the permissions dump query against the current database<br /> $auditCmd.CommandText = $UserDumpQueryText<br /> $queryresults = $auditCmd.ExecuteReader()<br /><br /> # Loop through permission dump resultset, print each value<br /> foreach ($row in $queryresults)<br /> {<br /> echo $row["ServerName"]<br /> echo $row["DatabaseName"]<br /> echo $row["LoginName"]<br /> echo $row["SelectAccess"]<br /> echo $row["InsertAccess"]<br /> echo $row["UpdateAccess"]<br /> echo $row["DeleteAccess"]<br /> echo $row["DBOAccess"]<br /> echo $row["SysadminAccess"]<br /> }<br /> }<br /> }<br /> }<br /></pre><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-745245149526898387?l=www.real-sql-guy.com' alt='' /></div>Tracy McKibbenhttp://www.blogger.com/profile/18326025369398096349noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-51816285752375491902011-09-14T05:30:00.000-07:002011-09-15T08:07:29.244-07:00Automated Permissions Auditing With Powershell and T-SQL: Part 2<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/-niYDNZDRZo0/Tm4kqMrG21I/AAAAAAAAH-k/lYXkMQexArg/s1600/spy.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-niYDNZDRZo0/Tm4kqMrG21I/AAAAAAAAH-k/lYXkMQexArg/s1600/spy.jpg" /></a></div><div>In <a href="http://www.real-sql-guy.com/2011/09/automated-permissions-auditing-with.html">Part 1</a> of this series, I presented a T-SQL query that will return a summary of login permissions within a SQL 2005/2008 database. &nbsp;I also mentioned that SQL 2000 requires a slightly different query.</div><div><br /></div><div>Here is that query.</div><div><br /></div><div>In the upcoming Part 3, I will start diving into the Powershell script that drives this process - stay tuned!</div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><pre class="brush: sql"><br />SELECT<br /> ServerName = @@SERVERNAME,<br /> LoginName = AccessSummary.LoginName,<br /> LoginType = CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,<br /> DatabaseName = DB_NAME(),<br /> SelectAccess = MAX(AccessSummary.SelectAccess),<br /> InsertAccess = MAX(AccessSummary.InsertAccess),<br /> UpdateAccess = MAX(AccessSummary.UpdateAccess),<br /> DeleteAccess = MAX(AccessSummary.DeleteAccess),<br /> DBOAccess = MAX(AccessSummary.DBOAccess),<br /> SysadminAccess = MAX(AccessSummary.SysadminAccess)<br />FROM<br /> (<br /> /* Get logins with permissions */<br /> SELECT <br /> LoginName = sysusers.name,<br /> SelectAccess = CASE WHEN sysprotects.action = 193 THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN sysprotects.action = 195 THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN sysprotects.action = 196 THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN sysprotects.action = 197 THEN 1 ELSE 0 END,<br /> DBOAccess = 0,<br /> SysadminAccess = 0<br /> FROM dbo.sysobjects<br /> INNER JOIN dbo.sysprotects<br /> ON sysprotects.id = sysobjects.id<br /> INNER JOIN dbo.sysusers<br /> ON sysusers.uid = sysprotects.uid<br /> INNER JOIN MASTER.dbo.syslogins AS syslogins<br /> ON syslogins.sid = sysusers.sid<br /> WHERE sysobjects.TYPE IN ('U', 'V')<br /> AND sysusers.issqlrole = 0<br /> AND sysprotects.protecttype IN (204, 205)<br /> AND sysprotects.action IN (193, 195, 196, 197)<br /> UNION ALL<br /> /* Get group members with permissions */<br /> SELECT <br /> LoginName = sysusersMember.name,<br /> SelectAccess = CASE WHEN sysprotects.action = 193 THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN sysprotects.action = 195 THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN sysprotects.action = 196 THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN sysprotects.action = 197 THEN 1 ELSE 0 END,<br /> DBOAccess = 0,<br /> SysadminAccess = 0<br /> FROM dbo.sysobjects<br /> INNER JOIN dbo.sysprotects<br /> ON sysprotects.id = sysobjects.id<br /> INNER JOIN dbo.sysusers AS sysusersRole<br /> ON sysusersRole.uid = sysprotects.uid<br /> INNER JOIN dbo.sysmembers<br /> ON sysmembers.groupuid = sysusersRole.uid<br /> INNER JOIN dbo.sysusers AS sysusersMember<br /> ON sysusersMember.uid = sysmembers.memberuid<br /> INNER JOIN MASTER.dbo.syslogins AS syslogins<br /> ON syslogins.sid = sysusersMember.sid<br /> WHERE sysobjects.TYPE IN ('U', 'V')<br /> AND sysusersRole.issqlrole = 1<br /> AND sysusersRole.name NOT IN ('public')<br /> AND sysprotects.protecttype IN (204, 205)<br /> AND sysprotects.action IN (193, 195, 196, 197)<br /> UNION ALL<br /> /* Get users in db_owner, db_datareader and db_datawriter */<br /> SELECT<br /> LoginName = syslogins.name,<br /> SelectAccess = CASE WHEN sysusers.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN sysusers.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> DBOAccess = CASE WHEN sysusers.name IN ('db_owner') THEN 1 ELSE 0 END,<br /> SysadminAccess = 0<br /> FROM dbo.sysusers<br /> INNER JOIN dbo.sysmembers<br /> ON sysmembers.groupuid = sysusers.uid<br /> INNER JOIN dbo.sysusers AS sysusersMember<br /> ON sysusersMember.uid = sysmembers.memberuid<br /> INNER JOIN MASTER.dbo.syslogins AS syslogins<br /> ON syslogins.sid = sysusersMember.sid<br /> WHERE sysusers.name IN ('db_owner', 'db_datareader', 'db_datawriter')<br /> UNION ALL<br /> /* Get users in sysadmin */<br /> SELECT<br /> LoginName = syslogins.name,<br /> SelectAccess = 1,<br /> InsertAccess = 1,<br /> UpdateAccess = 1,<br /> DeleteAccess = 1,<br /> DBOAccess = 0,<br /> SysadminAccess = 1<br /> FROM MASTER.dbo.syslogins AS syslogins<br /> WHERE syslogins.sysadmin = 1<br /> ) AS AccessSummary<br />INNER JOIN MASTER.dbo.syslogins AS syslogins<br /> ON syslogins.loginname = AccessSummary.LoginName<br />WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\\SQLSERVERAGENT')<br />GROUP BY<br /> AccessSummary.LoginName,<br /> CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END<br /></pre></div><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-5181628575237549190?l=www.real-sql-guy.com' alt='' /></div>Tracy McKibbenhttp://www.blogger.com/profile/18326025369398096349noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-70337098452035958692011-09-13T07:08:00.000-07:002011-09-14T06:36:45.245-07:00Bad For Babies and Bad For SQL Server<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-2iltaFnVy9A/Tm9c79UKSDI/AAAAAAAAAAs/W3kt1smw6wU/s1600/sqltree.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="200" src="http://3.bp.blogspot.com/-2iltaFnVy9A/Tm9c79UKSDI/AAAAAAAAAAs/W3kt1smw6wU/s200/sqltree.JPG" width="161" /></a></div>The life of a superhero isn't all fame and glory, most days the only excitement that we see is rescuing a cat from a tree, or in my case, saving a bit of T-SQL code from a terrible fate, as in this next story.<br /><br />I encountered a real villain, a nasty sort of fellow who liked to steal candy from babies. &nbsp;The nogoodnik was using a SQL Server database to record his evil deeds. &nbsp;When I tracked him down, he was attempting to build some reports that would allow him to summarize his nefarious activities.<br /><br />What I saw made me sick, and it's going to be very difficult to describe for you, but I'll do my best. &nbsp;So that you may truly appreciate what I was up against, I've included some sample code below. &nbsp;I apologize for what you're about to see, women and small children should leave the room now.<br /><br />First, let's create some sample data to represent the villain's database. &nbsp;Note that the real data was many times larger than this, the meanie had really been busy.<br /><br /><pre class="brush: sql"><br />CREATE TABLE Baby<br /> (<br /> BabyID INT NOT NULL PRIMARY KEY CLUSTERED,<br /> BabyName VARCHAR(255) NOT NULL<br /> );<br /><br />CREATE TABLE Candy<br /> (<br /> CandyID INT NOT NULL PRIMARY KEY CLUSTERED,<br /> CandyForm CHAR(4) NOT NULL,<br /> CandyFlavor VARCHAR(25) NOT NULL<br /> );<br /><br />CREATE TABLE CandySwipeEvent<br /> (<br /> EventID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,<br /> BabyID INT NOT NULL FOREIGN KEY REFERENCES Baby(BabyID),<br /> CandyID INT NOT NULL FOREIGN KEY REFERENCES Candy(CandyID),<br /> EventDate DATETIME NOT NULL,<br /> Weather VARCHAR(25) NOT NULL,<br /> BabyReaction VARCHAR(25) NOT NULL<br /> );<br /><br />INSERT INTO Baby (BabyID, BabyName) VALUES (1, 'Billy');<br />INSERT INTO Baby (BabyID, BabyName) VALUES (2, 'Timmy');<br />INSERT INTO Baby (BabyID, BabyName) VALUES (3, 'Susie');<br />INSERT INTO Baby (BabyID, BabyName) VALUES (4, 'Butch');<br />INSERT INTO Baby (BabyID, BabyName) VALUES (5, 'Josephina');<br />INSERT INTO Baby (BabyID, BabyName) VALUES (6, 'Stevie');<br /><br />INSERT INTO Candy (CandyID, CandyForm, CandyFlavor) VALUES (1, 'Bar', 'Chocolate');<br />INSERT INTO Candy (CandyID, CandyForm, CandyFlavor) VALUES (2, 'Bar', 'Peanut Butter');<br />INSERT INTO Candy (CandyID, CandyForm, CandyFlavor) VALUES (3, 'Bag', 'Chocolate');<br />INSERT INTO Candy (CandyID, CandyForm, CandyFlavor) VALUES (4, 'Pop', 'Fruit');<br />INSERT INTO Candy (CandyID, CandyForm, CandyFlavor) VALUES (5, 'Gel', 'Rainbow Worm')<br /><br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (1, 1, '12/25/1973 06:45am', 'Snowing', 'Cried');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (1, 2, '12/25/1974 06:07am', 'Cloudy', 'Cried');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (1, 1, '12/25/1975 04:31am', 'Snowing', 'Cried');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (1, 5, '12/25/1988 10:29am', 'Snowing', 'Punched me');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (1, 4, '12/25/1992 1:47pm', 'Sunny', 'Called police');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (2, 3, '4/1/1978 10:13am', 'Sunny', 'Cried');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (2, 3, '4/2/1978 10:17am', 'Sunny', 'Cried');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (2, 3, '4/3/1978 10:31am', 'Sunny', 'Cried');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (3, 4, '7/4/1983 2:49pm', 'Raining', 'Dog bit me');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (4, 4, '7/4/1983 3:03pm', 'Raining', 'Pulled knife');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (5, 5, '9/1/1986 4:51pm', 'Sunny', 'Cried');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (5, 5, '10/31/1986 8:19pm', 'Cloudy', 'Cried');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (6, 1, '10/31/1986 8:26pm', 'Cloudy', 'Cried');<br />INSERT INTO CandySwipeEvent (BabyID, CandyID, EventDate, Weather, BabyReaction) VALUES (6, 1, '10/31/1986 8:27pm', 'Cloudy', 'Cried');<br /></pre><br />Our evil genius was trying to write a query that would show him the last time that he had taken chocolate candy on a cloudy day, from any baby in his long history. &nbsp;Somehow, he had mangled together this bit of code - makes me cold all over just to look at it:<br /><br /><pre class="brush: sql"><br />SELECT<br /> BabiesToProcess.BabyID,<br /> (<br /> SELECT TOP 1 CandySwipeEvent.EventID<br /> FROM CandySwipeEvent<br /> INNER JOIN Candy<br /> ON CandySwipeEvent.CandyID = Candy.CandyID<br /> INNER JOIN Baby<br /> ON CandySwipeEvent.BabyID = Baby.BabyID<br /> WHERE CandySwipeEvent.Weather = 'Cloudy'<br /> AND Candy.CandyFlavor = 'Chocolate'<br /> AND CandySwipeEvent.BabyID = BabiesToProcess.BabyID<br /> ORDER BY CandySwipeEvent.EventDate DESC<br /> ) AS CandySwipeEventID<br />FROM<br /> (<br /> SELECT TOP (100) PERCENT BabyID<br /> FROM Baby<br /> ORDER BY BabyID<br /> ) AS BabiesToProcess<br />WHERE EXISTS<br /> (<br /> SELECT TOP 1 CandySwipeEvent.EventID<br /> FROM CandySwipeEvent<br /> INNER JOIN Candy<br /> ON CandySwipeEvent.CandyID = Candy.CandyID<br /> INNER JOIN Baby<br /> ON CandySwipeEvent.BabyID = Baby.BabyID<br /> WHERE CandySwipeEvent.Weather = 'Cloudy'<br /> AND Candy.CandyFlavor = 'Chocolate'<br /> AND CandySwipeEvent.BabyID = BabiesToProcess.BabyID<br /> ORDER BY CandySwipeEvent.EventDate DESC<br /> );<br /></pre><br />A closer look at the query plan for his monstrous creation shows that each table in the query is traversed twice, sometimes as a full scan. &nbsp;I mentioned earlier that the real database was quite large - the real CandySwipeEvent table contains 59 million rows, the Candy table 30 million, and the Baby table 19 million.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-fiyEFp_V3pg/Tm9iolcf7uI/AAAAAAAAAA0/Ha_CLKLTRRo/s1600/original_plan.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" width="620" src="http://3.bp.blogspot.com/-fiyEFp_V3pg/Tm9iolcf7uI/AAAAAAAAAA0/Ha_CLKLTRRo/s320/original_plan.jpg" /></a></div><br />After studying this for a moment, and realizing what his ultimate evil goal really was, I summoned all of my limited powers to produce this:<br /><br /><pre class="brush: sql"><br />SELECT<br /> BabyID,<br /> CandySwipeEventID<br />FROM<br /> (<br /> SELECT<br /> BabyID = Baby.BabyID,<br /> CandySwipeEventID = CandySwipeEvent.EventID,<br /> SwipeDateSequence = ROW_NUMBER() OVER(PARTITION BY Baby.BabyID ORDER BY CandySwipeEvent.EventDate DESC)<br /> FROM CandySwipeEvent<br /> INNER JOIN Baby<br /> ON CandySwipeEvent.BabyID = Baby.BabyID<br /> INNER JOIN Candy<br /> ON CandySwipeEvent.CandyID = Candy.CandyID<br /> WHERE CandySwipeEvent.Weather = 'Cloudy'<br /> AND Candy.CandyFlavor = 'Chocolate'<br /> ) AS SequencedSwipeEvents<br />WHERE SwipeDateSequence = 1;<br /></pre><br />The result is a much cleaner, more efficient query plan. &nbsp;I could have improved it further by doing some index analysis on the CandySwipeEvent table to eliminate that scan, but my powers were exhausted by this point.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-rEQusE2o424/Tm9jYKiz-yI/AAAAAAAAAA8/7XE1tfKnAXk/s1600/new_plan.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" width="620" src="http://1.bp.blogspot.com/-rEQusE2o424/Tm9jYKiz-yI/AAAAAAAAAA8/7XE1tfKnAXk/s320/new_plan.jpg" /></a></div><br />If I'd had my way, this evil-doer would have been permanently dispatched that day, but alas, the SuperHero Rulebook prevents such things, and he's still out there, somewhere, torturing T-SQL and stealing candy from babies.<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-7033709845203595869?l=www.real-sql-guy.com' alt='' /></div>REAL SQL Guyhttp://www.blogger.com/profile/07486104775304135831noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-65666971427293954162011-09-12T08:18:00.000-07:002011-09-14T06:31:04.038-07:00Automated Permissions Auditing With Powershell and T-SQL: Part 1<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-niYDNZDRZo0/Tm4kqMrG21I/AAAAAAAAH-k/lYXkMQexArg/s1600/spy.jpg" imageanchor="1" style="clear:left; float:left;margin-right:1em; margin-bottom:1em"><img border="0" height="171" width="171" src="http://3.bp.blogspot.com/-niYDNZDRZo0/Tm4kqMrG21I/AAAAAAAAH-k/lYXkMQexArg/s400/spy.jpg" /></a></div>A big part of my job is controlling security to multiple production databases, and reporting on that security for quarterly audits. &nbsp;In my environment, it's not important to know who has access to specific tables or other objects, but we interested in knowing who has some form of read or write access to a database, or who might be a DBO or sysadmin.<br /><br />This was largely a manual process, until I decided to dive into Powershell. &nbsp;Once I began to realize the power of Powershell (pun intended), I began to scheme ways that it could help provide better visibility into our database security.<br /><br />After a few iterations, I finally settled on a combination of Powershell and T-SQL. &nbsp;It's not terribly complicated, but the overall script is quite large, so I'll present it here in functional parts, and make the overall script available.<br /><br />Without further ado, let's begin...<br /><br />The first functional piece of the audit script that I'd like to present is the T-SQL query used to collect login details from my SQL 2005 and SQL 2008 servers. &nbsp;A different query is used for SQL 2000, which I'll present later. &nbsp;The Powershell script determines the version of a particular SQL Server instance and decides which query to run - I'll present this part later as well.<br /><br />This query returns every login from a SQL 2005/2008 database, including bits to indicate that the login has read access, write access, DBO access, or sysadmin access.<br /><br /><br /><pre class="brush: sql"><br />SELECT<br /> ServerName = @@SERVERNAME,<br /> LoginName = AccessSummary.LoginName,<br /> LoginType = CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,<br /> DatabaseName = DB_NAME(),<br /> SelectAccess = MAX(AccessSummary.SelectAccess),<br /> InsertAccess = MAX(AccessSummary.InsertAccess),<br /> UpdateAccess = MAX(AccessSummary.UpdateAccess),<br /> DeleteAccess = MAX(AccessSummary.DeleteAccess),<br /> DBOAccess = MAX(AccessSummary.DBOAccess),<br /> SysadminAccess = MAX(AccessSummary.SysadminAccess)<br />FROM<br /> (<br /> /* Get logins with permissions */<br /> SELECT <br /> LoginName = sysDatabasePrincipal.name,<br /> SelectAccess = CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,<br /> DBOAccess = 0,<br /> SysadminAccess = 0<br /> FROM sys.database_permissions AS sysDatabasePermission<br /> INNER JOIN sys.database_principals AS sysDatabasePrincipal<br /> ON sysDatabasePrincipal.principal_id = sysDatabasePermission.grantee_principal_id<br /> INNER JOIN sys.server_principals AS sysServerPrincipal<br /> ON sysServerPrincipal.sid = sysDatabasePrincipal.sid<br /> WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'<br /> AND sysDatabasePrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')<br /> AND sysServerPrincipal.is_disabled = 0<br /> UNION ALL<br /> /* Get group members with permissions */<br /> SELECT <br /> LoginName = sysDatabasePrincipalMember.name,<br /> SelectAccess = CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,<br /> DBOAccess = 0,<br /> SysadminAccess = 0<br /> FROM sys.database_permissions AS sysDatabasePermission<br /> INNER JOIN sys.database_principals AS sysDatabasePrincipalRole<br /> ON sysDatabasePrincipalRole.principal_id = sysDatabasePermission.grantee_principal_id<br /> INNER JOIN sys.database_role_members AS sysDatabaseRoleMember<br /> ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id<br /> INNER JOIN sys.database_principals AS sysDatabasePrincipalMember<br /> ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id<br /> INNER JOIN sys.server_principals AS sysServerPrincipal<br /> ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid<br /> WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'<br /> AND sysDatabasePrincipalRole.type_desc = 'DATABASE_ROLE'<br /> AND sysDatabasePrincipalRole.name <> 'public'<br /> AND sysDatabasePrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')<br /> AND sysServerPrincipal.is_disabled = 0<br /> UNION ALL<br /> /* Get users in db_owner, db_datareader and db_datawriter */<br /> SELECT<br /> LoginName = sysServerPrincipal.name,<br /> SelectAccess = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,<br /> InsertAccess = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> UpdateAccess = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> DeleteAccess = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,<br /> DBOAccess = CASE WHEN sysDatabasePrincipalRole.name = 'db_owner' THEN 1 ELSE 0 END,<br /> SysadminAccess = 0<br /> FROM sys.database_principals AS sysDatabasePrincipalRole<br /> INNER JOIN sys.database_role_members AS sysDatabaseRoleMember<br /> ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id<br /> INNER JOIN sys.database_principals AS sysDatabasePrincipalMember<br /> ON sysDatabasePrincipalMember.principal_id = sysDatabaseeRoleMember.member_principal_id<br /> INNER JOIN sys.server_principals AS sysServerPrincipal<br /> ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid<br /> WHERE sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader', 'db_datawriter')<br /> AND sysServerPrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')<br /> AND sysServerPrincipal.is_disabled = 0<br /> UNION ALL<br /> /* Get users in sysadmin */<br /> SELECT<br /> LoginName = sysServerPrincipalMember.name,<br /> SelectAccess = 1,<br /> InsertAccess = 1,<br /> UpdateAccess = 1,<br /> DeleteAccess = 1,<br /> DBOAccess = 0,<br /> SysadminAccess = 1<br /> FROM sys.server_principals AS sysServerPrincipalRole<br /> INNER JOIN sys.server_role_members AS sysServerRoleMember<br /> ON sysServerRoleMember.role_principal_id = sysServerPrincipalRole.principal_id<br /> INNER JOIN sys.server_principals AS sysServerPrincipalMember<br /> ON sysServerPrincipalMember.principal_id = sysServerRoleMember.member_principal_id<br /> WHERE sysServerPrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')<br /> AND sysServerPrincipalMember.is_disabled = 0<br /> ) AS AccessSummary<br />INNER JOIN MASTER.dbo.syslogins AS syslogins<br /> ON syslogins.loginname = AccessSummary.LoginName<br />WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT')<br />GROUP BY<br /> AccessSummary.LoginName,<br /> CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END<br /></pre><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-6566697142729395416?l=www.real-sql-guy.com' alt='' /></div>Tracy McKibbenhttp://www.blogger.com/profile/18326025369398096349noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-58315821854263024552011-09-09T14:27:00.000-07:002011-09-14T06:24:07.674-07:00SYS.PARTITIONS: Better Than X-Ray VisionOver the past few months, I've been doing a lot of work with the table and index compression introduced in SQL Server 2008. If you've worked with this stuff much, you know that, in an existing database, it involves index rebuilds - lots and lots of index rebuilds. In my case, that also means lots of waiting, because we have some very large tables. This is truly a case where "start it and go get coffee" applies. The question, however, is how long do you have to get that coffee? I spent enough time waiting around that I decided to try to answer that question, and I found that the answer is lurking in the <b>sys.partitions</b> table.<br /><br />Assume that we have a table named <b>ExamRegistration</b>, with multiple indexes, one of which is named <b>IX_ExamRegistration_Disposition</b>:<br /><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-a1PEPq6cD4w/Tmp7Jl17OPI/AAAAAAAAAAQ/CW53lUdag2g/s1600/ExamReg1.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-a1PEPq6cD4w/Tmp7Jl17OPI/AAAAAAAAAAQ/CW53lUdag2g/s640/ExamReg1.jpg" width="620" /></a></div><br /><br /><br /><br />The <b>sys.partitions</b>&nbsp;table can tell us some additional things about this index, such as the number of rows, and its compression status:<br /><pre class="brush: sql"><br />SELECT *<br /> FROM sys.partitions<br /> WHERE OBJECT_ID = OBJECT_ID('dbo.ExamRegistration')<br /> AND index_id = INDEXPROPERTY(OBJECT_ID('dbo.ExamRegistration'), 'IX_ExamRegistration_Disposition', 'IndexID')<br /></pre><br />From this, we can see that this index contains just over 5 million rows, and is currently not compressed. We're going to compress it, and use the <b>sys.partitions</b> table to track the progress of that compression effort.<br /><br /><pre class="brush: sql"><br />ALTER INDEX IX_ExamRegistration_Disposition ON dbo.ExamRegistration REBUILD<br /> WITH (ONLINE=ON, DATA_COMPRESSION=PAGE)<br /></pre><br />Before we proceed, I'd like to make a comment on this ALTER statement. Note that it is doing an ONLINE rebuild. I do the vast majority of my index rebuilds ONLINE, allowing me to work while the system is live, with minimal disruption to the users. It's also important to note that this <b>sys.partitions</b> trick won't work with OFFLINE rebuilds. The rebuild places locks that prevent querying the <b>sys.partitions</b> table. With that said, let's move on.<br /><br />If we re-run the <b>sys.partitions</b> query while the index rebuild is taking place, we'll see something interesting:<br /><pre class="brush: sql"><br />SELECT *<br /> FROM sys.partitions<br /> WHERE OBJECT_ID = OBJECT_ID('dbo.ExamRegistration')<br /> AND index_id = INDEXPROPERTY(OBJECT_ID('dbo.ExamRegistration'), 'IX_ExamRegistration_Disposition', 'IndexID')<br /></pre><br />There are now TWO rows in <b>sys.partitions</b> for our index! Notice the difference in the row counts - this is how we can track the progress of the index rebuild. As the rebuild progresses, the row count of the new index will increase until it matches the original index. Once the rebuild completes, the original index is dropped, and <b>sys.partitions</b> will again return only one row, but that row now shows us the compressed index:<br /><div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-dzf_ieBbvLk/TmqAgj-ikBI/AAAAAAAAAAo/g_9xKrSdg0o/s1600/ExamReg5.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-dzf_ieBbvLk/TmqAgj-ikBI/AAAAAAAAAAo/g_9xKrSdg0o/s640/ExamReg5.JPG" width="620" /></a></div><br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-5831582185426302455?l=www.real-sql-guy.com' alt='' /></div>REAL SQL Guyhttp://www.blogger.com/profile/07486104775304135831noreply@blogger.com0tag:blogger.com,1999:blog-2146276824340207020.post-70591477191055581162011-09-08T18:30:00.000-07:002011-09-08T19:10:53.197-07:00REAL SQL Guy Lives Again!From 2006 through early 2008, I published a blog, "realsqlguy.com", with aspirations of becoming the next online SQL Server celebrity. &nbsp;Ok, nothing that grand, but it was intended to be a dumping ground for useful, interesting, or even completely off-the-wall SQL Server stuff.<br /><br />The blog died, for a few reasons:<br /><ul><li>real life demands</li><li>I wasn't very good at the blogging game</li><li>I forgot to renew the domain name</li></ul><div>I gave up and let REAL SQL Guy slip away quietly, assuming I would never see him again.</div><div><br /></div><div>Fast-forward to present day. &nbsp;I'm the proud owner of another blog, dedicated to a personal hobby, that is fairly successful. &nbsp;I'm more confident in my writing abilities, or at least more willing to accept that I can't please or impress everybody. &nbsp;The demands of real life have eased somewhat, and I have more time to devote to writing. &nbsp;Most importantly, I'm a more skilled DBA than I was five years ago. &nbsp;Granted, I'm still no expert, but I do have things to share now, and the odds of posting something "right" vs something "wrong" are greater now.</div><div><br /></div><div>I decided to try to revive REAL SQL Guy, but was disappointed to find that the "realsqlguy.com" domain name is now in the hands of a cybersquatter, who wants $2000 to sign the domain back over to me. &nbsp;I politely said "Your nuts!" and moved on. &nbsp;Luckily, the "real-sql-guy.com" domain was still available, and as of today, belongs to me, and is probably how you came to be reading this right now.</div><div><br /></div><div>REAL SQL Guy lives again, and I'm going to treat him better this time around.</div><div><br /></div><br /><br /><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/2146276824340207020-7059147719105558116?l=www.real-sql-guy.com' alt='' /></div>Tracy McKibbenhttp://www.blogger.com/profile/18326025369398096349noreply@blogger.com2