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:
DECLARE @loop int = 1
DECLARE @username sysname
DECLARE @orphanedusers TABLE
      id int identity(1, 1) ,
      UserName sysname ,
      UserSID varchar(36)
INSERT  INTO @orphanedusers
        ( [UserName], [UserSID] )
        EXEC sp_change_users_login 'report'

WHILE @loop <= ( SELECT MAX(id)
                 FROM   @orphanedusers
        SET @username = ( SELECT    UserName
                          FROM      @orphanedusers
                          WHERE     id = @loop
        IF @username IN ( SELECT    name
                          FROM      master.sys.[syslogins] )
                EXEC sp_change_users_login 'auto_fix', @username      
        SET @loop = @loop + 1


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.