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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Strategies » SQL Server 2005 Best Practices - How Best To...

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

Rate Topic

Display Mode

Topic Options

Author

Message

YSLGuru

Posted Tuesday, December 01, 2009 8:56 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:38 PM
Points: 846, Visits: 1,451

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

GSquared

Posted Tuesday, December 01, 2009 9:06 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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 »

Permissions