Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Changing db owner script Expand / Collapse
Author
Message
Posted Wednesday, February 9, 2011 11:17 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:42 PM
Points: 209, Visits: 641
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

Post #1061409
Posted Wednesday, February 9, 2011 11:29 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1061424
Posted Wednesday, February 9, 2011 11:36 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:42 PM
Points: 209, Visits: 641
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
Post #1061434
Posted Wednesday, February 9, 2011 11:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 1,345, Visits: 2,541
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
Post #1061442
Posted Wednesday, February 9, 2011 12:35 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 8,573, Visits: 9,081
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
Post #1061492
Posted Wednesday, February 9, 2011 12:57 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:42 PM
Points: 209, Visits: 641
Thanks...will have to read up on the CMS and give you an update as to what takes place... thats for the input...

DHeath
Post #1061506
Posted Wednesday, February 9, 2011 3:29 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:42 PM
Points: 209, Visits: 641
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
Post #1061597
Posted Thursday, February 10, 2011 8:06 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 8,573, Visits: 9,081
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
Post #1062018
Posted Thursday, February 10, 2011 9:02 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:42 PM
Points: 209, Visits: 641
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
Post #1062076
Posted Thursday, February 10, 2011 9:41 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:42 PM
Points: 209, Visits: 641
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
Post #1062102
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse