Orphan users

After a database restoration you always need to fix the orphan users (between the restored database and the master database)

Here is a script that is doing that for a database. You only need to precise the database you want to use (use databasename must be replaced by the concerned database)

/*------------------------------------------- 
--    This Job parses the whole users in 
--    previously restored database 
--    and autofix the logins 
--------------------------------------------- 
-- 
--    Created by: Arnaud Degraeve 
--    Date: 2012-16-03 
--    Email: sqlserver@arnaud-degraeve.com 
-- 
-------------------------------------------*/

---------------------------------- 
-- Variables Declaration 
---------------------------------- 
DECLARE @login nvarchar(50)

---------------------------------- 
-- Variables setting 
---------------------------------- 
USE DatabaseName

---------------------------------- 
--    Prepares a cursor containing 
--    the users 
---------------------------------- 
DECLARE logins_cursor CURSOR FOR       
SELECT
          l.name       
FROM
          sys.database_principals u 
          INNER  JOIN ys.server_principals l 
          ON u.sid=l.sid

---------------------------------- 
--    Open the cursor 
---------------------------------- 
OPEN logins_cursor

FETCH NEXT
             FROM logins_cursor INTO @login

WHILE @@FETCH_STATUS = 0             
     BEGIN                   
     ----------------------------------                   
     --    Autofix each user                   
     ----------------------------------                
     EXEC sp_change_users_login 'Auto_Fix', @login                
     FETCH NEXT FROM logins_cursor INTO @login

END

---------------------------------- 
-- Closes cursor and releases 
--    the memory 
---------------------------------- 
CLOSE logins_cursor 
DEALLOCATE logins_cursor