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;'