Wednesday, 3 July 2013

A Script to Fix Orphaned Users

I find that one (well, actually, one of many) really annoying things of restoring databases to different servers is the orphaned user! I am notorious for forgetting about them. Here's a common scenario in my office:
Colleague: Hey DBA, can you refresh a copy of the database on the QA server
Me: Sure
Me (after restore finishes): Hey colleague, it's done.
Colleague: Hey DBA! I CAN'T LOG IN!
Me: *!*&^%$$£"!"£$%"£$
So, in order to help me with this, I've written a little script to make my life a little easier:

Hope this helps.


Uwe Ricken said...

Hi Thomas,

Best practise for such scenarios is moving the SQL Accounts with sp_help_revlogin!

We use this in a mirrored Environment with success :)

Best, Uwe

Thomas Mucha said...

Thanks Uwe,

That's very helpful.