x

Change DB owner for all databases?

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
more ▼

asked Aug 14, 2012 at 07:04 AM in Default

SumitRana gravatar image

SumitRana
180 6 9 11

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
EXEC sp_MSforeachdb 'EXEC [?]..sp_changedbowner ''sa'' '
more ▼

answered Aug 14, 2012 at 07:27 AM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

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, 2012 at 07:30 AM SumitRana

add another single quote to the end

EXEC sp_MSforeachdb 'EXEC [?]..sp_changedbowner ''sa'''
Aug 14, 2012 at 07:42 AM Kev Riley ♦♦

Stick another ' at the end, thus:

EXEC sp_MSforeachdb 'EXEC [?]..sp_changedbowner ''sa'''
Aug 14, 2012 at 07:42 AM ThomasRushton ♦
Thanks, I missed that single quote, I made the changes in my post.
Aug 14, 2012 at 07:46 AM Cyborg
thanks to all..
Aug 14, 2012 at 07:54 AM SumitRana
(comments are locked)
10|1200 characters needed characters left

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')
more ▼

answered Aug 14, 2012 at 02:51 PM

SirSQL gravatar image

SirSQL
4.8k 1 3

(comments are locked)
10|1200 characters needed characters left

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()}
more ▼

answered Aug 14, 2012 at 03:41 PM

Shawn_Melton gravatar image

Shawn_Melton
5.3k 19 21 29

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1839

asked: Aug 14, 2012 at 07:04 AM

Seen: 3734 times

Last Updated: Aug 14, 2012 at 03:41 PM