USE [master]
GO
CREATE PROC [dbo].[ap_adm_OrphanUsersFix]
AS

	SET NOCOUNT ON  
	DECLARE @cmd varchar(4000) 

	BEGIN TRY
		
		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
	----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 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 
								print @cmd
								Execute (@cmd)
							END 
					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
			RETURN
	END CATCH

 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 
                                                                print @cmd
                                                                Execute (@cmd)
                                                        END 
                                        FETCH NEXT FROM MC INTO @cmd 
										END 
                        CLOSE MC 
                        DEALLOCATE MC

SELECT  ERROR_NUMBER()ErrorNumber,
                                ERROR_LINE() AS ErrorLine,
                                ERROR_MESSAGE() AS ErrorMessage

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 

/*
    EXECute a string of T-SQL commands over the contents
  of a set, replacing token strings with column values as
  indicated.  The set is defined by the @FROM parameter.
  Optionally allows for printing the final execution string,
  switching databases, and user-specified TRY-CATCH handling.
  Also allows the caller to switch quote characters, to ease
  quote-doubling problems.
  
  RBarryYoung   Proactive Performance Solutions, Inc.
*/
ALTER PROC 
    OVER_SET (
        @command AS NVARCHAR(MAX),      -- Template SQL command
        @from   AS NVARCHAR(MAX),       -- FROM..WHERE clause string
        @subs1  AS NVARCHAR(MAX) = N'', -- Substitution parameters, these are
        @subs2  AS NVARCHAR(MAX) = N'', --  of the form  "<find>=<repl>"  where:
        @subs3  AS NVARCHAR(MAX) = N'', --    <find> will be searched for in @command, and
                                        --    <repl> will replace it, if it was found
                                        --  (typically, <repl> should be a column name
                                        --      returned by the FROM clause)
        @print  AS BIT = 1,             -- 0 = suppress PRINT of the SQL before executing
        @catch  AS VARCHAR(12) = 'continue',
                                        -- TRY/CATCH option parameters.  Choices are:
                                        --  'continue' on an error, print a message & continue
                                        --  'ignore'   attempt to suppress all errors
                                        --  'raise'    try to re-raise the error
                                        --  'none'     no TRY/CATCH blocks
        @use_db AS NVARCHAR(255) = N'', -- DB to switch to befor execution of the SQL text
        @quote  AS NVARCHAR(8) = N''    -- search for this character & replace with (').
            )
 AS
 --
DECLARE @qt AS NVARCHAR(1), @cr AS NVARCHAR(1);
SELECT  @qt = N'''',        @cr = N'
';
DECLARE @find1  AS NVARCHAR(MAX), @prfx1  AS NVARCHAR(MAX), @sufx1 AS NVARCHAR(MAX)
DECLARE @find2  AS NVARCHAR(MAX), @prfx2  AS NVARCHAR(MAX), @sufx2 AS NVARCHAR(MAX)
DECLARE @find3  AS NVARCHAR(MAX), @prfx3  AS NVARCHAR(MAX), @sufx3 AS NVARCHAR(MAX)
DECLARE @prtst  AS NVARCHAR(MAX), @prfxC  AS NVARCHAR(MAX), @sufxC AS NVARCHAR(MAX)
DECLARE @newdb  AS NVARCHAR(MAX), @declr  AS NVARCHAR(MAX)
DECLARE @NewCmd AS NVARCHAR(MAX), @GenCmd AS NVARCHAR(MAX)
;
SELECT
 @find1 = CASE WHEN @subs1 = N'' THEN N'' ELSE LEFT(@subs1,CHARINDEX(N'=',@subs1)-1) END,
 @prfx1 = CASE WHEN @subs1 = N'' THEN N'' ELSE N'REPLACE(' END,
 @sufx1 = CASE WHEN @subs1 = N'' THEN N'' ELSE N',@find1,'+RIGHT(@subs1,LEN(@subs1)-CHARINDEX(N'=',@subs1))+N')' END,
 @find2 = CASE WHEN @subs2 = N'' THEN N'' ELSE LEFT(@subs2,CHARINDEX(N'=',@subs2)-1) END,
 @prfx2 = CASE WHEN @subs2 = N'' THEN N'' ELSE N'REPLACE(' END,
 @sufx2 = CASE WHEN @subs2 = N'' THEN N'' ELSE N',@find2,'+RIGHT(@subs2,LEN(@subs2)-CHARINDEX(N'=',@subs2))+N')' END,
 @find3 = CASE WHEN @subs3 = N'' THEN N'' ELSE LEFT(@subs3,CHARINDEX(N'=',@subs3)-1) END,
 @prfx3 = CASE WHEN @subs3 = N'' THEN N'' ELSE N'REPLACE(' END,
 @sufx3 = CASE WHEN @subs3 = N'' THEN N'' ELSE N',@find3,'+RIGHT(@subs3,LEN(@subs3)-CHARINDEX(N'=',@subs3))+N')' END,
 @newdb = CASE WHEN @use_db= N'' THEN N'' ELSE N'USE [' + @use_db + N'];' + @cr END,
 @declr = N'DECLARE @_Num AS INT, @_Lin AS INT, @_Err AS NVARCHAR(MAX), @_Msg AS NVARCHAR(MAX);'+@cr
;
;WITH
 [base] AS (SELECT cmd = @command),
 [quot] AS (SELECT cmd = CASE @quote WHEN N'' THEN cmd ELSE REPLACE(cmd, @quote, @qt) END FROM [base]),
 [dble] AS (SELECT cmd = N'N'+@qt+REPLACE(cmd, @qt, @qt+@qt)+@qt FROM [quot]),
 [prnt] AS (SELECT cmd = CASE @print WHEN 1 THEN N'    PRINT '+cmd+';'+@cr ELSE N'' END
                       + N'    EXEC('+cmd+N');'  FROM [dble]),
 [ctch] AS (SELECT cmd = 
    CASE @catch WHEN N'none' THEN cmd 
    ELSE N'BEGIN TRY'+@cr+cmd+@cr+N'END TRY'+@cr+N'BEGIN CATCH'+@cr
    + N'    SELECT @_Num=ERROR_NUMBER(), @_Lin=ERROR_LINE(), @_Err=ERROR_MESSAGE()'+@cr
    + CASE @catch
        WHEN N'continue' THEN 
                N'    SELECT @_msg=''Continuing after Error(''+CAST(@_Num AS NVARCHAR)+'') at Line ''+CAST(@_Lin AS NVARCHAR)+'''
                                    +@cr+'    ''+@_Err;'+@cr
               +N'    PRINT @_msg; '+@cr
               +N'    PRINT '' ''; '+@cr
        WHEN N'ignore' THEN N'    -- ignore = do nothing'+@cr
        WHEN N'fail' THEN
                N'    SELECT @_msg=''Failing after Error(''+CAST(@_Num AS NVARCHAR)+'') at Line ''+CAST(@_Lin AS NVARCHAR)+'''
                                    +@cr+'    ''+@_Err;'+@cr
               +N'    RAISERROR(@_Num, 16, 1);'+@cr
               +N'    PRINT '' ''; '+@cr
        ELSE N'  --BAD else branch, shouldnt get here' END
    + N'END CATCH;' END  FROM [prnt])
SELECT 
    @NewCmd  = @prfx1+@prfx2+@prfx3+ N'@command' +@sufx1+@sufx2+@sufx3,
    @command = cmd + @cr
FROM [ctch]
;
SELECT @GenCmd = '
DECLARE @sql AS NVARCHAR(MAX); SET @sql = '''+@newdb+ +@declr+ '''
;WITH 
  [-@from]  AS ( SELECT * FROM ' +@from+ ' )
, [-@subs]  AS ( SELECT [-NewCmd] = ' +@NewCmd+ ' FROM [-@from] )
, [-@print] AS ( SELECT [-NewCmd] = [-NewCmd] FROM [-@subs] )
SELECT 
    @sql = @sql + ''
'' + [-NewCmd]
FROM [-@subs]
;
EXEC sp_executesql @sql;
'
;
EXEC sp_executesql @GenCmd
, N'@command NVARCHAR(MAX), @from NVARCHAR(MAX), @find1 NVARCHAR(MAX), @find2 NVARCHAR(MAX), @find3 NVARCHAR(MAX)'
, @command, @from, @find1, @find2, @find3
;

EXEC OVER_SET '
    EXEC OVER_SET "
        ALTER USER [{name}]  WITH LOGIN = [{name}]; ",
        @from = "sys.database_principals
            WHERE ( type_desc = ""WINDOWS_GROUP"" OR type_desc = ""WINDOWS_USER"" )
            AND  name NOT like ""%dbo%"" AND name NOT LIKE  ""%#%"" ",
        @use_db = "{db}",
        @subs1  = "{name}=name",
        @catch  = "continue",
        @print  = 1,
        @quote  = "`";
        ',
    @from = 'sys.sysdatabases
        WHERE dbid > 4',
    @subs1 = '{db}=name',
    @catch = 'continue',
    @print = 0,
    @quote = '"';