Post reply

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

  • December 1, 2009 at 8:56 am

    #218172

    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!

  • December 1, 2009 at 9:06 am

    #1085885

    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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply