/*
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('''')),''
'','''')
IF LEN(FixDude) > 15
BEGIN
SELECT @FixDude =''USE [?]
'' + @FixDude
PRINT @FixDude
EXEC (@FixDude)
END'
RETURN
Comments :
Post a Comment