LinkedIn | FaceBook | Twitter
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.sqlAuthor: Buck WoodyPurpose: 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 fixusersEND
Also - Greg sent me this great link to his script that checks the backups like I did a couple of posts back. Thanks, Greg!
You may have had the problem where you move a database from one server to another and lose the ability