Post reply

Change Object Ownership

  • February 5, 2009 at 9:26 am

    #130599

    SQL Server 2005 newbie.

    User created objects which, instead of dbo, are associated with his account. How can we change this?

    Many thanks!

  • February 5, 2009 at 9:49 am

    #938991

    http://msdn.microsoft.com/en-us/library/ms173423.aspx

  • February 5, 2009 at 9:54 am

    #938996

    Yeah, after I asked the question on behalf of the developer, he figured it out himself. 😉

  • February 5, 2009 at 9:57 am

    #939000

    You might change the schema that owns the object as

    ALTER SCHEMA schema_name TRANSFER object_name

    Thanks!!

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • February 5, 2009 at 10:05 am

    #939008

    And if you have lots of them to change:

    SELECT 'ALTER SCHEMA NewSchema TRANSFER ' + '['+TABLE_SCHEMA+']' + '.' + TABLE_NAME

    FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'OldSchema'

    order by TABLE_NAME

Viewing 5 posts - 1 through 4 (of 4 total)

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