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

avatar image

SumitRana
180 8 9 14

(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

avatar image

Cyborg
10.8k 37 55 51

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

avatar image

SirSQL
4.9k 4 5

(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

IR | foreach-object {$.SetOwner('sa'); $.Refresh()}

more ▼

answered Aug 14, 2012 at 03:41 PM

avatar image

Shawn_Melton
6.4k 21 25 34

(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.

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:

x2091

asked: Aug 14, 2012 at 07:04 AM

Seen: 10550 times

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

Copyright 2016 Redgate Software. Privacy Policy