Skip to content

Instantly share code, notes, and snippets.

@ghotz
Created May 8, 2024 16:01
Show Gist options
  • Save ghotz/a5969e283073d08eae231bfba8a1ec42 to your computer and use it in GitHub Desktop.
Save ghotz/a5969e283073d08eae231bfba8a1ec42 to your computer and use it in GitHub Desktop.
Evaluate and fix non sa database owner
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