Change DB Owner to sa for multiple DB's

Riken Patel, 2013-04-26 (first published: 2013-04-12)

Executing this script will change the DB owner to sa for all Online DB's where owner is not already sa.

There is no reason that a DB owner should be a normal login, at some point you might want to remove that login from your server and you will get an error saying that they own the DB.

USE master GO SET NOCOUNT ON /*Generate an execution script for each database that needs the owner changed*/DECLARE @LoopExecuteScripts TABLE (exec_command VARCHAR(1000)) INSERT INTO @LoopExecuteScripts SELECT 'ALTER AUTHORIZATION ON DATABASE::' + name + ' TO sa' FROM sys.databases WHERE owner_sid <> 0x01 AND state_desc = 'ONLINE' /*Loop through each record in @LoopExecuteScripts table and execute the command*/DECLARE @Current_Record VARCHAR(1000) SELECT @Current_Record = MIN(exec_command) FROM @LoopExecuteScripts WHILE @Current_Record IS NOT NULL BEGIN PRINT @Current_Record EXEC (@Current_Record) --Delete processed record DELETE FROM @LoopExecuteScripts WHERE exec_command = @Current_Record --Get next record to be processed SELECT @Current_Record = MIN(exec_command) FROM @LoopExecuteScripts END

Fixed Database Roles

by Andy Warren

SQLServerCentral.com

Security

This article covers four of the fixed database roles (db_datareader, db_datawriter, db_denydatareader, and db_denydatawriter). If you're new to SQL security (and maybe even if you're not) this article is worth reading.