DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = ';WITH step1 as(' + Util.dbo.StringConcat('SELECT ''' + d.name + ''' COLLATE SQL_Latin1_General_CP1_CI_AS AS DBName, ''USE [' + d.name + ']; /*DECLARE @SQL VARCHAR(MAX)*/ SELECT @SQL = Util.dbo.StringConcat(''''ALTER AUTHORIZATION ON SCHEMA::['''' + s.name + ''''] TO [dbo];'''', '''' '''') FROM ' + d.name + '.sys.schemas s INNER JOIN ' + d.name + '.sys.database_principals p ON p.principal_id = s.principal_id WHERE p.NAME = '''''' + d.name + '''''' IF @SQL <> '''''''' EXEC(@SQL); DROP USER ['' + d.name + ''];'' COLLATE SQL_Latin1_General_CP1_CI_AS AS DropSQL,
sp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS SPName, sp.sid AS SPsid, d.name COLLATE SQL_Latin1_General_CP1_CI_AS AS [name], d.principal_id, d.[type] COLLATE SQL_Latin1_General_CP1_CI_AS AS [type], d.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS as type_desc, d.default_schema_name COLLATE SQL_Latin1_General_CP1_CI_AS as default_schema_name, d.create_date, d.modify_date, d.owning_principal_id, d.[sid], d.is_fixed_role
FROM ' + d.name + '.sys.database_principals d
LEFT OUTER JOIN sys.server_principals sp ON sp.name COLLATE SQL_Latin1_General_CP1_CI_AS = d.name COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE d.sid IS NOT NULL AND NOT exists(SELECT * FROM sys.server_principals s WHERE s.sid = d.SID)
AND d.is_fixed_role = 0
AND d.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''public'', ''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
AND D.type COLLATE SQL_Latin1_General_CP1_CI_AS IN (''G'', ''U'')', '
UNION ALL
') + ')
SELECT * FROM step1'
FROM sys.databases AS d
EXEC Util.dbo.PrintLargeText @SQL
EXEC(@SQL)