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 SYSNAME	NULL	,UserName SYSNAME	,UserSID VARCHAR(2000))
DECLARE @MissingLoginsTable TABLE
	(ID INT IDENTITY(1,1) ,UserName SYSNAME)

DECLARE myCursor CURSOR FOR
	SELECT	name
	FROM	sys.databases
	WHERE	name 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
		WHERE	UserName IN (select name from sys.syslogins);
	END

	-- Orphans users corresponding to which No Login exists
	INSERT INTO @MissingLoginsTable
	SELECT	UserName 
	FROM	@OrphanLoginsTable
	WHERE	UserName NOT IN (SELECT p.name FROM sys.database_role_members rm
							JOIN sys.database_principals p ON rm.role_principal_id = p.principal_id)
	AND		UserName NOT IN ('dbo')
	AND		UserName NOT IN (select name from sys.syslogins)
	AND		UserName 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