PWNED! (from beyond the grave)

OK first off if you don’t know the definition of the word PWNED here is a tutorial…

So now that you’re well versed on pwned or pwnage let’s talk about a way to ensure that the DBA (who we’ll call “Brent”) that left the company does not get all zombie and start eating your brain after he’s gone.

**RING RING RING**

You: Hello?

Them: Where is my report?

You: How did you get this number?

Them: Where is my report?

You:  What report?

Them:  Wow… Brent was way smarter then you *click*

(and yes this is a shout out to Brent Ozar and a huge congratz to Jeremiah Peschka!!  not saying Brent would ever do this but it’s the buzz of the SQL Twitterverse this morning so I thought I had to work it in somehow )

The first thing to check when something goes wrong after someone has left the company is if anything could of been tied to that user’s Active Directory account because chances are they are now disabled/deleted/removed/zombified.

“So here’s a little script that’s mine… you might want to execute line by line… don’t worry….. be happy.”

(as always any and all scripts on this blog should never be ran… k that’s a bit much… you can run it but test it, tweak it, and make sure it works for you in your environment )

I wrote this script to not care if the instance is the default or a named instance (why you may ask? well cause I use Powershell to run this script against all my servers and return me any reports that are not owned by a particular user):

SET NOCOUNT ON
 
DECLARE @InstanceName nvarchar(100)
DECLARE @ReportServerDatabaseName nvarchar(100)
DECLARE @ReportingDatabaseFoundInd bit
DECLARE @SQLSTMT nvarchar(2000)
 
SELECT @InstanceName = @@SERVERNAME
SELECT @ReportingDatabaseFoundInd = 0
 
IF @InstanceName = SUBSTRING(@InstanceName, ISNULL(CHARINDEX('\', @InstanceName)+1, 0), LEN(@InstanceName))
	BEGIN
		SELECT	@ReportServerDatabaseName = 'ReportServer'
	END
ELSE
	BEGIN
		SELECT	@ReportServerDatabaseName = 'ReportServer' + '$' + SUBSTRING(@InstanceName, ISNULL(CHARINDEX('\', @InstanceName)+1, 0), LEN(@InstanceName))
	END
 
SELECT	@ReportingDatabaseFoundInd = 1
FROM	master.dbo.sysdatabases
WHERE	name = @ReportServerDatabaseName
 
IF @ReportingDatabaseFoundInd = 1
	BEGIN
		SELECT @SQLSTMT = 'SELECT	@@SERVERNAME as [InstanceName],
									U.UserName as [SubscriptionOwner], C.[Path],
									S.[description]
							FROM	' + @ReportServerDatabaseName +
								'.dbo.subscriptions S INNER JOIN ' +
								@ReportServerDatabaseName + '.dbo.users U ON U.userid = S.ownerid INNER JOIN ' +
								@ReportServerDatabaseName + '.dbo.[Catalog] C ON S.Report_OID = C.ItemID'
		EXEC sp_executesql @SQLSTMT
	END

Now if this list is a bit HUGE you can add a WHERE clause in there to look for a particular user name.

So now that you’ve found all the reports that are tied to the no longer existant user how do you change them?

DECLARE @OldID uniqueidentifier
DECLARE @NewID uniqueidentifier
SELECT @OldID = UserID FROM dbo.Users WHERE UserName = 'Domain\LoginName'
SELECT @NewID = UserID FROM dbo.Users WHERE UserName = 'Domain\LoginName'
UPDATE dbo.Subscriptions SET OwnerID = @NewID WHERE OwnerID = @OldID

A gotcha to watch out for with this is that the new ID that you set the report to needs to be in the users table (in your ReportServer database) so if you want to change it to a user that is not already in there you’ll have to log in as that user and add/update/delete a report to get them in there. (There is probably an easier way but that has always worked for me)

Enjoy!!