Posts by :
PWNED! (from beyond the grave)
August 18th, 2010OK 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
ENDNow 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!!
Blog Laryngitis
August 17th, 2010It’s been a bit since I’ve written a blog post and just like anything else if you don’t do it, you lose it and I’m having a hard time figuring out new topics to write about. I’ve been very busy recently but alot of what I’ve been doing has been stuff that I’ve written about before or stuff that 100 people have already posted about.
I’m trying to get myself back into a “blogging groove” as the summer months have sucked all ambition out of me to do anything other then spend some quality time with wifey and the kidlets. Not that spending time with the family is a bad thing but like everything else in life it’s about balance
So here is my first of hopefully many more blog posts to help get me started on getting my “voice” back and to get back in touch with the community that I think is the bestest out there!
Enjoy!!
EDMPASS – August Meeting w/Aaron Nelson
August 6th, 2010On August 25th 2010 the Edmonton Chapter of PASS is having it’s next meeting. Details below:
http://www.eventbrite.com/event/792195478
Please be sure to not only click the “Add to my calendar” but also the “Register” button so that we can plan accordingly for food and drinks.
Date: August 25th 2010
Time: 5:00 pm – 7:00 pm
Location: Stanley A. Milner library
Map: 7 Sir Winston Churchill Square
Meeting Room: Centennial Room – Basement
Speaker: Aaron Nelson
Topic: Powershell for Database Professionals
Session Abstract:
In this session we will begin exploring the amazing world of the PowerShell language. We will learn to perform several everyday DBA tasks like backing up user databases, scripting table objects and evaluating disk space usage with PowerShell; then we will change these into multi-server scripts by adding only a single line of code. We will also take a look at some data visualization techniques that require only a small amount of code. Only a basic understanding of PowerShell or DOS is needed. This session should serve as a good introduction to PowerShell for database users. The goal is to get data professionals feet wet about PowerShell and ready to practice it when they leave. Yes you will get a copy of the scripts I use.
Presenter Information: Aaron Nelson (@SQLvariant) sqlvariant.com/wordpress/
Aaron Nelson is a Senior SQL Server Architect with over 10 years experience in architecture, business intelligence, development, and performance tuning of SQL Server.
He has experience managing enterprise-wide data needs in both transactional and data warehouse environments. Aaron holds certifications for MCITP: Business Intelligence Developer, Database Administrator, Database Developer; as well as MCTS: Windows Server Virtualization, Configuration (meaning Hyper-V).
Agenda:
5:00 pm – Pizza and Socializing
5:30 pm – Sponsor Presentation
5:45 pm – Feature Presentation
7:00 pm – Wrap Up and Draws
If you haven’t signed up already at EDMPASS.com please do so now to receive meeting notifications, news, and updates from EDMPASS.
Hope to see you there.
Enjoy!!
PASS Summit 2010… I just can’t say no
July 15th, 2010
As some of you know my sister-in-law is getting married in the Dominican this November and I’ve been having an inner battle about whether or not the Summit would be in the cards for me in 2010. After some thought and weighing my options I’ve come to the conclusion that I just can’t miss the Summit. Well… can’t miss SOME of the Summit I guess.
I’ll be flying into Seattle on the Sunday (Nov 7th) and flying out in the evening on the Wednesday (Nov 10th)… I know I’ll be missing a whole day of sessions on the Thursday (and the post-cons on the Friday) but this is the only way I could make it work. Lucky for me as the chapter president for EDMPASS I’ll get the DVDs so I can be sure to catch all the Friday sessions I missed… yeah it’s not live but it’s better then not seeing them at all.
I can’t remember the last time I’ve been away from work for 2 weeks… I think the last time I took 2 weeks off was for the birth of my son (4 years ago this August). That’s how important the Summit is to me professionally and personally. Last year was my first Summit and I can’t say enough about how great the speakers and SQL community is. I’ll be attending my first ever pre-con this year (I figured if I’m missng a whole day on Thursday that I should get in a “deep dive” pre-con session on the Monday).
+ 
November is looking like it’s gonna be a crazy month but it’s a good crazy, quality time with the family (and my first ever ocean… I know… how lame and I? LOL) mixed in with quality time with some of my favorite SQL geeks… look out Seattle @BenchmarkIT is coming back to town…..
Enjoy!!
Hi I’m Policy Based Management… Remember me?
June 22nd, 2010Microsoft released the SQL Server 2008 R2 Best Practices Analyzer last week and I’m not going to lie I was quite shocked. I’ve yet to give it a test drive but the first thing that came to my head was…
Why wouldn’t they just release a PSP?
No not THAT kind of PSP…. PSP is my acronym for “Policy Service Pack”
Policy Based Management is/was/should be the replacement for the Best Practices Analyzer so why now are we bringing the BPA back? To me it would just make more sense to release some additional policies to add to the 50+ “Best Practice” policies that already are included with SQL Server 2008.
Now granted (as mentioned above) I have yet to install and give it a test drive so perhaps I’m missing something but in my opinion releasing this tool will affect the adoption rate of DBAs using Policy Based Management.
I’m downloading and installing now…. follow up blog to come
Enjoy!!
