sp_AutoFixUsers

This stored procedure checks for orphaned users and fixces them.

use master
go
create procedure sp_AutoFixUsers
as
set nocount on
 
DECLARE @OrphanedUsers TABLE
(
  OrphandedUserId int identity(1,1) primary key,
  UserName sysname,
  UserSID  varbinary(85)
)
INSERT INTO @OrphanedUsers
EXEC sp_change_users_login 'report'
 
if not exists (select 1 from @OrphanedUsers)
begin
       print 'No orphaned users found in database '+quotename(db_name(db_id()))
end
 
DECLARE @LoginName sysname
 
DECLARE orphaned_cursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
SELECT UserName FROM @OrphanedUsers
 
OPEN orphaned_cursor  
FETCH NEXT FROM orphaned_cursor INTO @LoginName  
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
       print char(10)+ 'Orphaned user ' + quotename(@LoginName)+' found'
       EXEC sp_change_users_login @Action = 'Auto_Fix' , @UserNamePattern = @LoginName
       print char(10)
    FETCH NEXT FROM orphaned_cursor INTO @LoginName
END  
 
CLOSE orphaned_cursor  
DEALLOCATE orphaned_cursor;

Because this stored procedure is created in the master database with the prefix sp_ it's registered as system stored procedure.  With this feature on it's possible to call this stored procedure from any database on the instance.

single database call example

use <YourDatabase>; exec sp_AutoFixUsers;

Run on every database in one single statement

exec sp_MsForEachDb 'use [?]; exec sp_AutoFixUsers;'