Colleague: Hey DBA, can you refresh a copy of the database on the QA serverSo, in order to help me with this, I've written a little script to make my life a little easier:
Me: Sure
Me (after restore finishes): Hey colleague, it's done.
Colleague: Hey DBA! I CAN'T LOG IN!
Me: *!*&^%$$£"!"£$%"£$
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 ) BEGIN SET @username = ( SELECT UserName FROM @orphanedusers WHERE id = @loop ) IF @username IN ( SELECT name FROM master.sys.[syslogins] ) BEGIN EXEC sp_change_users_login 'auto_fix', @username END SET @loop = @loop + 1 END
Hope this helps.
2 comments:
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
Thanks Uwe,
That's very helpful.
Tom
Post a Comment