Change DB owner for all databases?

Question

I want to know, is there any script so i can change DB owner to SA for all databases in sql server in one attempt? please help me out if some have info. i will be thankfull.

sumit

Answer 1

0

EXEC sp_MSforeachdb 'EXEC [?]..sp_changedbowner ''sa'' '

answered Aug 14 '12 at 07:27 AM

Cyborg
10.2k ● 29 ● 39 ● 44

when i run i EXEC sp_MSforeachdb 'EXEC [?]..sp_changedbowner ''sa'' am getting below error:

Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string 'EXEC [?]..sp_changedbowner 'sa''.

Aug 14 '12 at 07:30 AM SumitRana

add another single quote to the end

EXEC sp_MSforeachdb 'EXEC [?]..sp_changedbowner ''sa'''

Aug 14 '12 at 07:42 AM Kev Riley ♦♦

Stick another ' at the end, thus:

EXEC sp_MSforeachdb 'EXEC [?]..sp_changedbowner ''sa'''

Aug 14 '12 at 07:42 AM ThomasRushton ♦

Thanks, I missed that single quote, I made the changes in my post.

Aug 14 '12 at 07:46 AM Cyborg

thanks to all..

Aug 14 '12 at 07:54 AM SumitRana

(comments are locked)

Answer 2

0

I wouldn't use sp_msforeachdb as it has a habit of missing databases completely. I prefer to use a query to build the execution strings so that I can be sure of what I'm executing.

Run the following and paste the results back to the query window for executing:

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [sa];' 
from sys.databases
    where name not in ('master', 'model', 'tempdb')

answered Aug 14 '12 at 02:51 PM

SirSQL
4.6k ● 1 ● 3

(comments are locked)

Answer 3

0

You might also like the PowerShell way...

From Buck Woody:


#if you have named instance it would be \SQL\server\instance\Databases I beleive
cd MSSQLSERVER:\SQL\server\Databases

# Set the owner for all objects in a directory   
DIR | foreach-object {$_.SetOwner('sa'); $_.Refresh()}

answered Aug 14 '12 at 03:41 PM

Shawn_Melton
4.7k ● 13 ● 17 ● 27

(comments are locked)

Change DB owner for all databases?

Follow this question

Related Questions