SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing db owner script


Changing db owner script

Author
Message
DHeath
DHeath
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 650
Hello to all,

Thanks for your time and hopefully this wont be too difficult to answer.

I need to change 30-40 databases per server (about 20+ servers) and looking for a script that can change the database owner NOT table owner. I have looked around and have not been successful in finding a script that will satisfy this request even within SSC unless i missed it whis is VERY possible. Anyways...I know i can use
EXEC dbo.sp_changedbowner @loginame = ''sa'', @map = false
but that changes one at a time and i am not sure how to incorporate that in a script that will make it a much faster solution. Also i do think that 2005 and 2008 will use the same syntax. Also just for double checking i dont believe that changing the database owner to 'sa' will cause any issues but always like to ask just to reassure before making the change.

Thanks again

Dheath
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39583 Visits: 9730
I'll recommend Red Gate's Multiscript product for that kind of thing.

If you can't get that (check the link at the top of this site), then a dynamic SQL script that queries sys.databases and runs the script you have against each one, is going to be easiest. You'll have to manually run that on each server, but it will get the job done.

Multiscript will be easier, and if you're administering multiple servers, it'll pay for itself pretty rapidly.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
DHeath
DHeath
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 650
Thanks for the reply G-Squared =]

I am hoping to find something that is on the "free" side as you know how companies dont want to spend any $$$ they dont have too. So was looking to find someone that may have already written one that could be shared. I can find many that changes the table owner but none that changes the database owner. But i do appreciate your response.

DHeath
Robert klimes
Robert klimes
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3723 Visits: 3456
You could try creating a central management server(http://msdn.microsoft.com/en-us/library/bb934126.aspx) an then use
sp_msforeachdb 'USE [?]; EXEC dbo.sp_changedbowner @loginame = ''sa'', @map = false'



Bob
-----------------------------------------------------------------------------
How to post to get the best help
Tom Thomson
Tom Thomson
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20982 Visits: 12422
Robert klimes (2/9/2011)
You could try creating a central management server(http://msdn.microsoft.com/en-us/library/bb934126.aspx) an then use
sp_msforeachdb 'USE [?]; EXEC dbo.sp_changedbowner @loginame = ''sa'', @map = false'



As sp_changedbowner is deprecated in SQL 2008, it may be better to use
sp_msforeachdb 'ALTER AUTHORISATION DATABASE [?] TO sa' 

(if I've got that right - I always used sp_changedbowner, haven't used the new thing yet)

Tom

DHeath
DHeath
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 650
Thanks...will have to read up on the CMS and give you an update as to what takes place... thats for the input...

DHeath
DHeath
DHeath
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 650
Ok update.... have tried to use the code snippit of

EXEC sp_MSforeachdb 'USE ?; EXEC sp_changedbowner ''sa'''

and running that on the master database but it errors out with

Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.
Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.
Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.

i have tried on SQL 2008 and 2005 with both returning the same error messages...any ideas? Also this also errors out if its ran on a user database with the same errors.

DHeath
Tom Thomson
Tom Thomson
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20982 Visits: 12422
DHeath (2/9/2011)
Ok update.... have tried to use the code snippit of

EXEC sp_MSforeachdb 'USE ?; EXEC sp_changedbowner ''sa'''

and running that on the master database but it errors out with

Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.
Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.
Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.

i have tried on SQL 2008 and 2005 with both returning the same error messages...any ideas? Also this also errors out if its ran on a user database with the same errors.

DHeath

basically the set of databases has to be filtered t exclude those four.

For SQL 2008 you can use a script something like

Use MASTER
GO
DECLARE @sqlBase nvarchar(300) = 'ALTER AUTHORIZATION DATABASE XXX TO SA'+char(13)+char(10)
DECLARE @sql nvarchar(max)=''
SELECT @sql = @sql+'REPLACE(sqlBase,''XXX'',name) FROM sys.databases'
WHERE name NOT IN ('MASTER','MODEL','TEMPDB','DISTRIBUTION')
EXEC (@sql)


but your user/login has to have either CREATE DATABASE permission in MASTER or have server level ALTER ANY DATABASE permission in order for this script to have any chance to work if any databases which are currently off-line (maybe also if any databases are currently in standby?).
If you need to change the owner of MASTER,MODEL,TEMPDB, or DISTRIBUTION either there's something horribly wrong with your installation or you are doing something really strange.

Tom

DHeath
DHeath
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 650
If you need to change the owner of MASTER,MODEL,TEMPDB, or DISTRIBUTION either there's something horribly wrong with your installation or you are doing something really strange.


Tom Thomson
Na tog mi gun tuit mi ach ma thuiteas tog!
Thig crìoch air an t-saoghal ach mairidh gaol is ceòl


Thanks a TON!! this is just what i needed Tom, its greatly appreciated and i am sure there will be others looking to use it as well. As for the System databases i have not came across any servers where they are not owned by 'sa' just yet but i honestly would not be surprised IF i have some in that situation. i am guessing i will cross that bridge when i get there. Remember i am coming into this environemt and trying to get things tidy and structured which is a process that will take quite a bit of time and just aiming to do things correctly. Thanks again

DHeath
DHeath
DHeath
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 650
This is a nice piece of code that will generate the needed code to make the change as well. Was written by a co-worker for me and if you see the need for it...enjoy.

-- code generator to change dbowner to 'sa' for databases not owned by 'sa'

declare @dbname sysname
declare c_loop cursor for
select name from sysdatabases
where sid <> 0x01
order by name

open c_loop
fetch next from c_loop into @dbname
while @@fetch_status <> -1 begin -- determines loop is continuing
if @@fetch_status <> -2 begin -- determines record is still available (not dirty)
print 'use ' + @dbname
print 'g' + 'o'
print 'exec sp_changedbowner ''sa'''
print 'g' + 'o'
print ''
end
fetch next from c_loop into @dbname
end
close c_loop
deallocate c_loop


DHeath
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search