Orphaned Users

With the SQL Server 2005 end date approaching, you may find yourself migrating databases.  One of the gotchas with any database migration is orphaned users.  Below is a script I put together, and have been using for years, to help me resolve issues with orphaned users:

CREATE TABLE #temporphans
  (
     username VARCHAR(100),
     usersid  VARCHAR(100),
     dbname   VARCHAR(100) NULL
  )

EXEC sp_msforeachdb
  @command1 =' USE [?] INSERT INTO #temporphans (UserName, UserSID) EXEC [sp_change_users_login] @Action=''Report''; UPDATE #temporphans SET dbName = ''?'' WHERE dbName IS NULL; '

SELECT dbname,
       username
FROM   #temporphans

DROP TABLE #temporphans


 From here you run an 'Update_one' action:

EXEC sp_change_users_login 'Update_One', [USER], [login]) 

You can update the script to include the concatenation you need to automate this if you like.


REF: sp_change_users_login