Script of the day - Fix orphaned logins

  • Comments 1

You may have had the problem where you move a database from one server to another and lose the ability for the users to log in. This happens because the SID values in the master database don't match the SID values in the user database, even if the same accounts are on both. You can use a built-in statement to fix this problem, but I use this script to find and fix them in all inside a user DB:

/*
usc_Fix_Orphaned_Logins.sql
Author:  Buck Woody
Purpose:  Fixes the orphaned users issue from one server to another.
*/


BEGIN
 DECLARE @username varchar(25)
 DECLARE fixusers CURSOR
 FOR
 SELECT UserName = name FROM sysusers
  WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
   and suser_sname(sid) is null
    ORDER BY name
 OPEN fixusers
 FETCH NEXT FROM fixusers
 INTO @username
 WHILE @@FETCH_STATUS = 0
 BEGIN
  EXEC sp_change_users_login 'update_one', @username, @username
  FETCH NEXT FROM fixusers
  INTO @username
 END
 CLOSE fixusers
 DEALLOCATE fixusers
END

 Also - Greg sent me this great link to his script that checks the backups like I did a couple of posts back. Thanks, Greg!

http://codetempest.blogspot.com/2007/08/good-query-for-checking-your-latest.html

 

Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post
  • You may have had the problem where you move a database from one server to another and lose the ability

Page 1 of 1 (1 items)