SQL : FIX ALL POTENTIALLY ORPHANED ACCOUNTS

/*
 SQL : FIX ALL POTENTIALLY ORPHANED ACCOUNTS
 Moving DBs, prompted me to conjure this up. 
 Situation: You moved dbs to a new server, do your best to sync
 previous account to new server, with SIDs intact, yet every other
 user that tries to connect ends up at your desk, WTM?
 
 Better safe than sorry. Autofix all orphaned account, and tell them to 
 "TRY NOW!" :-P
 
 Note: one error that came up was when a corresponding principal account
   does not exists at server level
*/

EXEC sp_MSForEachDB '
USE [?]
DECLARE @FixDude Varchar(8000)
SELECT 
 @FixDude = REPLACE((
SELECT DISTINCT 
    ''    EXEC sp_change_users_login ''''Auto_Fix'''' ,'' + QUOTENAME(name,char(39)) + ''
    ''
    FROM
    sys.database_principals
WHERE
    type = ''s''
    AND default_schema_name = ''dbo'' 
    AND name <> ''dbo'' 
FOR XML PATH('''')),''&#x0D;'','''')

IF LEN(FixDude) > 15
BEGIN
 SELECT @FixDude =''USE [?]
'' + @FixDude

 PRINT @FixDude
 EXEC (@FixDude)
END'
   
RETURN

Comments :

0 comments to “SQL : FIX ALL POTENTIALLY ORPHANED ACCOUNTS”