Fix Orphan Users for Instance Migration

/*Created By:AJAY DWIVEDI
Created Date:DEC 18, 2015
Purpose:Script to Fix Orphan Users for All databases
*/SET NOCOUNT ON;
DECLARE@ID INT
,@RecordsInserted INT
,@DBName SYSNAME
,@String NVARCHAR(4000)
,@Missing_Logins NVARCHAR(4000)

DECLARE@OrphanLoginsTable TABLE
(ID INT IDENTITY(1,1),DBName SYSNAMENULL,UserName SYSNAME,UserSID VARCHAR(2000))
DECLARE @MissingLoginsTable TABLE
(ID INT IDENTITY(1,1) ,UserName SYSNAME)

DECLARE myCursor CURSOR FOR
SELECTname
FROMsys.databases
WHEREname not in ('master','model','msdb','tempdb')

OPEN myCursor
FETCH NEXT FROM myCursor INTO @DBName;

-- Loop through databases
WHILE @@FETCH_STATUS = 0 
BEGIN 

SET@String = '
USE ['+@DBName+']
EXEC sp_change_users_login ''Report'';
';
INSERT INTO @OrphanLoginsTable
(UserName, UserSID)
EXEC sp_executesql @String;

SET @RecordsInserted = @@ROWCOUNT;
SET @ID = @@IDENTITY;

-- Orphans users for which logins already exists
IF@RecordsInserted <> 0
BEGIN
SELECT'USE ['+@DBName+'];
Exec sp_change_users_login ''auto_fix'','''+UserName+'''  
' 
FROM@OrphanLoginsTable
WHEREUserName IN (select name from sys.syslogins);
END

-- Orphans users corresponding to which No Login exists
INSERT INTO @MissingLoginsTable
SELECTUserName 
FROM@OrphanLoginsTable
WHEREUserName NOT IN (SELECT p.name FROM sys.database_role_members rm
JOIN sys.database_principals p ON rm.role_principal_id = p.principal_id)
ANDUserName NOT IN ('dbo')
ANDUserName NOT IN (select name from sys.syslogins)
ANDUserName NOT IN (SELECT UserName FROM @MissingLoginsTable);

DELETE FROM @OrphanLoginsTable;
FETCH NEXT FROM myCursor INTO @DBName;
END

IF (SELECT COUNT(1) FROM @MissingLoginsTable) > 0
BEGIN
SELECT@Missing_Logins=COALESCE(@Missing_Logins,'') + (CAST(ID AS VARCHAR(3))+ ') ' + UserName + '
')
FROM@MissingLoginsTable;

PRINT'
Below Users do not have corresponding Logins on Instance. Please migrate these Logins first:-

'+@Missing_Logins;
END

CLOSE myCursor 
DEALLOCATE myCursor