August 25, 2003 at 3:25 am
Hi All,
is there any way I can change the owner of all the objects in my database in one go.
there is a stored procedure for changing individual object ownership, but we have around 3000 objects whose owner needs to be changed. If I do this one by one then it would be very time consuming..
Thanks in advance
August 25, 2003 at 4:10 am
Possibly you can just update the system tables, havent tried to know if there gotchas looming. Just about as easy to just use a cursor to loop through - remember to exclude system objects!
Andy
August 25, 2003 at 4:21 am
I do not want to update the sytem tables. I just want to update the user table owner.
quote:
Possibly you can just update the system tables, havent tried to know if there gotchas looming. Just about as easy to just use a cursor to loop through - remember to exclude system objects!Andy
http://www.sqlservercentral.com/columnists/awarren/
August 25, 2003 at 5:30 am
Its not possible but you can write your own script to do at one go. And you can use this script in any database and server.
I am just giving outline of the SQL which will generate the batch for me to change the ownership of all objects which are not owned by dbo.
select 'exec sp_changeobjectowner ' + name + ', dbo' from sysobjects where type in ('U','P','V') and uid <> 1
Go
Run the output of the query as a batch.
August 26, 2003 at 12:31 am
Hi ...
Just a warning to BE VERY CAREFUL when messing with object ownerships in SQL. I once changed a bunch of table ownerships in my dB from dbo to a role and it was a total catastrophe. No-one was able to access them, not even sa. The only solution was to restore the dB from the previous nights backup.
Cheers
August 26, 2003 at 1:23 am
Select 'print '' object : ' + u.name + '.' + o.name + ''' ' + char(13) + ' go '+ char(13)
+ 'Sp_changeobjectowner ''' + u.name + '.' + o.name + ' '', ''NewSchema'' ' + char(13) + ' go '
from sysobjects o
inner join sysusers u
on (o.uid = u.uid)
where o.xtype in ('p', 'u', 'v') -- p = procedures, u = usertables , v = userviews
and u.name like 'oldschema%'
and o.name not in ('dtproperties', 'whatever you want to exclude')
order by u.name, o.name
This wil not correct your DTSses. There you should do a save-as :(.
If you run packages in sqlagent, you'll also have to adjust the name/Dts-id overthere.
Doublecheck views and sp's so the correct schema is used. (your view-definition-text will not be altered by sp_changeobjectowner )
Always try to use the propre system-sp's befor you mess with the systemtables.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 26, 2003 at 6:34 am
Since your just changing table owners, SQLDMO will work also. The nice thing about using SQLDMO, is that you can create it as a SQL Agent job (ActiveX/VBScript), which can be scheduled or run as needed
SET oServer = CreateObject("SQLDMO.SQLServer")
oServer.LoginSecure = True
oServer.Connect "MySQLServer"
SET oDatabase = oServer.Databases("MyDB")
FOR EACH oTable IN oDatabase.Tables
IF oTable.Owner <> "dbo" THEN
oTable.Owner = "dbo"
END IF
NEXT
SET oTable = Nothing
SET oDatabase = Nothing
SET oServer = Nothing
August 26, 2003 at 7:33 am
I have been going through the same thing. I did a mix of manually creating the change owner and a couple of automated scripts.
One problem I had was that the problem owner owned the PK on a number of tables. For some reason the SP_Changeobjectowner would not allow me to change those objects.
So I tried to drop them via EM, figuring I could just recreate them. I kept getting messages that it couldn't drop the PK as it was a contraint, but showed no contraints.
I was a pain, but I basically cloned the tables, there was only 5 or 6, dropped the originals, and renamed the clones to the proper name.
Never figured out why I could DROP or change owner on the PKs, but I did get around it.
KlK, MCSE
KlK
August 26, 2003 at 7:37 am
kknudson,
Did you have DRI (foreign keys) ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply