Created
May 8, 2024 16:01
-
-
Save ghotz/a5969e283073d08eae231bfba8a1ec42 to your computer and use it in GitHub Desktop.
Evaluate and fix non sa database owner
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
D1.[name] AS database_name, S1.[name] as [owner_name] | |
, CASE | |
WHEN S1.[name] = N'sa' | |
THEN NULL | |
--ELSE 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(D1.[name]) + ' TO [sa] -- was ' + QUOTENAME(S1.[name]) | |
ELSE 'USE ' + QUOTENAME(D1.[name]) + '; EXEC sp_changedbowner ''sa'' -- was ' + QUOTENAME(S1.[name]) | |
END AS alter_command | |
FROM sys.databases AS D1 | |
LEFT | |
JOIN sys.server_principals S1 | |
ON D1.owner_sid = S1.[sid] | |
WHERE D1.[name] NOT IN ('master','model','msdb','tempdb') |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment