USE [master]
GO
	SET NOCOUNT ON  
	DECLARE @cmd varchar(4000) 

	BEGIN TRY
		Create table #Orphan_User_Tbl 
		(
			[Database_Name] sysname , 
			[Orphaned_User] sysname 
		)
		SET NOCOUNT ON	

		Create table #Windows_Auth_Orphan_User
		(
		[Str] nvarchar(300)
		) 
		DECLARE @DBCount INT,@MaxCount INT,  @Qry nvarchar(4000), @DBName sysname
		DECLARE @db_list table (dbname nvarchar(100),ID int identity)
		SET @Qry = ''
		SET @DBCount = 1
				INSERT INTO @db_list(dbname )
					SELECT name FROM  sys.sysdatabases 
					WHERE dbid > 4
			
			SELECT @MaxCount = MAX(ID) FROM @db_list
			WHILE(@DBCount < = @MaxCount  )
				BEGIN
					SELECT @DBName =  dbname FROM @db_list WHERE id = @DBCount
					SET @Qry = '	SELECT ''' + @DBName + ''' AS [Database Name], 
							CAST(su.name AS sysname)   AS [Orphaned User]
							FROM ' + QUOTENAME(@DBName) + '..sysusers su
							inner join master..syslogins b
								on su.name=b.name
							where 
								su.sid is not null 
								and su.sid not in (0x00,0x01)
							and su.sid <> b.sid'
					INSERT INTO #Orphan_User_Tbl EXEC (@Qry)
					SET @DBCount = @DBCount + 1 
				END

			DECLARE MC CURSOR 
 			READ_ONLY 
 			FOR 
				
			SELECT [Database_Name]+ '..sp_change_users_login  ''UPDATE_ONE'' , ''' + Orphaned_User  +  ''' ,''' + Orphaned_User  +  ''';'
			FROM #Orphan_User_Tbl 
			ORDER BY [Database_Name], [Orphaned_User]
				
			OPEN MC 
			FETCH NEXT FROM MC INTO @cmd 
				WHILE (@@fetch_status <> -1) 
					BEGIN 
						IF (@@fetch_status <> -2) 
	 						BEGIN TRY
								PRINT  @cmd
								Execute (@cmd)
							END TRY
							BEGIN CATCH
								SELECT 	ERROR_NUMBER()ErrorNumber,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
								FETCH NEXT FROM MC INTO @cmd 
							CONTINUE;
							END CATCH	
					FETCH NEXT FROM MC INTO @cmd 
					END 
			
			CLOSE MC 
			DEALLOCATE MC 
---------------------------------------------------------------------------------------------------------
	----Fixing Windows autheticated user-----------------
		SET @DBCount = 1 		
			WHILE(@DBCount < = @MaxCount  )
				BEGIN
					SELECT @DBName =  ''+ dbname + '' FROM @db_list WHERE id = @DBCount
					SET @Qry =  'SELECT  '' USE [' +  @DBName + ' ]; 
									ALTER USER [''  + NAME +  '']  WITH LOGIN = [ '' +  NAME  + '']''  
									FROM msdb.sys.database_principals
									WHERE ( type_desc = ''WINDOWS_GROUP'' OR type_desc = ''WINDOWS_USER'' )
											AND  name NOT like ''%dbo%'' AND name NOT LIKE  ''%#%'''	
				INSERT INTO #Windows_Auth_Orphan_User EXEC (@Qry)
				SET @DBCount = @DBCount + 1 
				END		

		--SELECT * FROM #Windows_Auth_Orphan_User
		DECLARE MC CURSOR READ_ONLY FOR 
		SELECT [Str]FROM #Windows_Auth_Orphan_User
			OPEN MC 
			FETCH NEXT FROM MC INTO @cmd 
				WHILE (@@fetch_status <> -1) 
					BEGIN 
						IF (@@fetch_status <> -2) 
	 						BEGIN TRY
								PRINT  @cmd
								Execute (@cmd)
							END TRY
							BEGIN CATCH
								SELECT 	ERROR_NUMBER()ErrorNumber,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
								FETCH NEXT FROM MC INTO @cmd 
							CONTINUE;
							END CATCH	
					FETCH NEXT FROM MC INTO @cmd 
					END 
			CLOSE MC 
			DEALLOCATE MC 

		DROP Table #Orphan_User_Tbl
		Drop table #Windows_Auth_Orphan_User 

	END TRY

	BEGIN CATCH
		SELECT 	ERROR_NUMBER()ErrorNumber,
				ERROR_LINE() AS ErrorLine,
				ERROR_MESSAGE() AS ErrorMessage
	END CATCH

exec sp_MSforeachdb 'use ?; select ''use ? '' +  ''
go'' + ''
drop user '' + sdp.name from sys.database_principals sdp 
left join sys.server_principals ssp on sdp.sid = ssp.sid
where ssp.sid is null and sdp.type in (''S'',''U'',''G'')
and sdp.name not in (''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''BROKER_USER'', ''dbo'')'

-- 1) List all Orphaned users on existing DB
-- 2) Associate the DB User with the server Login, if existing
-- 3) If 2) fails, try to delete associated DB Schema
-- 4) Drop User

DECLARE @UserName varchar(100),
		@SQL as varchar(max)

CREATE TABLE
	#users (
			Username varchar(100),
			UserSID varbinary (85)
			)
-- 1)
INSERT INTO 
	#users 
	exec sp_change_users_login @Action='Report'

DECLARE mycurs CURSOR FOR 
	SELECT 
		Username 
	FROM
		#users
		
OPEN mycurs
FETCH NEXT FROM mycurs 
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN

	-- 2)
	BEGIN TRY
	EXEC sp_change_users_login @Action='update_one', @UserNamePattern=@UserName, @LoginName=@UserName
	END TRY

	BEGIN CATCH
		print @Username + ' does not exist as a Login. Deleting. '
		
		-- 3)
		BEGIN TRY 
			SET @SQL = 'DROP SCHEMA ' + @UserName
			EXEC (@SQL)
		END TRY
		BEGIN CATCH
		END CATCH
		
		-- 4)
		SET @SQL = 'DROP USER ' + @UserName
		EXEC (@SQL)
	END CATCH
    FETCH NEXT FROM mycurs 
    INTO @username
END

CLOSE mycurs
DEALLOCATE mycurs

DROP TABLE #users