Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 Best Practices - How Best To Change The Ownership Of Many Objects


SQL Server 2005 Best Practices - How Best To Change The Ownership Of Many Objects

Author
Message
YSLGuru
YSLGuru
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 1659
There are several posts in the SQLServerSentral.com Forums on this but what I'm looking for is input from multiple parties on what they feel is the "Best Practices' approach to performing this task and if possible to include sample code using one of the Microsoft Supplied sample databases so that readers of your post will have real world code as an example verses psudeocode.

I know that with recent changes (well maybe not recent bit within the past few releases of SQL Server) that a change with schemas was made so that SQL Server now supports a schema as being sperate from what it was or how it was treated in say SQL Server 7. Now Schemas can own objects where as before only DB Users could.

I've read about several examples but most opt to construct some type of procedure that outputs multiple T-SQL commands where each contains the "sp_changeobjectowner" Stored Procedure followed by the name of an existing object along with the name of what entity to set as the new owner of the object. While this works it would seem to me that with the change in how schemas work that there should be some way to do this object ownership change with multiple objects in a single line, at least multiple objects of the same type such as all tables.

Here is a psudeo code example of what I am referring to:

ALTER SCHEMA 
SET SCHEMA ON ALL TABLES
FOR OldSchemaName
TO NewSchemaName
GO



This is just an example of what I would imagine you should be able to do with the new SCHEMA syntax. Then again it may very well be that even with the new SCHEMA model you still have to change object ownership one object at a a time.


In any event please contribute to this posting what your Best Practices approach is to changing the Ownership for multiple objects of various types.

Thanks To all in advance

Kindest Regards,

Just say No to Facebook!
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
I'm pretty sure you have to change them one at a time. It's one of the few things cursors are really useful for.

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