Change DB Owner to sa for multiple DB's

Question

Post reply

Change DB Owner to sa for multiple DB's

Riken Patel

SSC Veteran

Points: 253
More actions
April 12, 2013 at 2:37 pm

#394575

Comments posted to this topic are about the item Change DB Owner to sa for multiple DB's

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ernest Libertucci Ten Centuries Points: 1394 More actions · Answer 1

use master

go

exec sp_msforeachdb 'USE ?;if not exists (select 1 from sys.databases where owner_sid = 0x01 and name = db_name()) exec sp_changedbowner ''sa'''

Riken Patel SSC Veteran Points: 253 More actions · Answer 2

sp_changedbowner is an old procedure which may be removed in future release.

sp_msforeachdb is undocumented so would rather not use in a production system, particularly as I have heard of a few problems with it, such as missing databases.

Ernest Libertucci Ten Centuries Points: 1394 More actions · Answer 3

Valid points, I was just exploring an alternative. No offense intended!

aleksey donskoy Mr or Mrs. 500 Points: 565 More actions · Answer 4

Good Script.

Two things:

1. Why do you need to keep the script in the temp table, and not execute it right away?

2. A databases with a spaces in a name would fail this script. Therefore modyfied the following line as:

SELECT 'ALTER AUTHORIZATION ON DATABASE::[' + name + '] TO sa'

Thanks.

Alex Donskoy

SQL Server DBA

Greeneberg Trauriq PA Miami FL

Riken Patel SSC Veteran Points: 253 More actions · Answer 5

Good spot on the spaces issue.

Not sure how you are thinking of doing it without a temp table, there would be a few ways but they would not be as tidy I don't think.

Also, with this method, you can substitute the query that inserts into the temp table for a different query and run that exec command, such as dropping multiple tables.

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 6

Nice script

The following is missing AND is_read_only = 0 in select

INSERT INTO @LoopExecuteScripts

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + name + ' TO sa'

FROM sys.databases

WHERE owner_sid <> 0x01

AND state_desc = 'ONLINE' --AND is_read_only = 0

and it will work without errors

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

Change DB Owner to sa for multiple DB's

Cookies on SQLServerCentral