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

SQL Server 2005 Best Practices - How Best To Change The Ownership Of Many Objects Expand / Collapse
Author
Message
Posted Tuesday, December 01, 2009 8:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, March 10, 2014 1:21 PM
Points: 885, Visits: 1,526
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,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.
Post #826762
Posted Tuesday, December 01, 2009 9:06 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #826770
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse