orphaned users

To detect orphaned users, execute the following Transact-SQL statements:

USE <database_name>

GO
sp_change_users_login @Action='Report'
GO

The following command relinks the server login account specified by <login_name> with the database user specified by <database_user>.

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
GO

To fix all Orphaned users in a DB:


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