Home Forums SQL Server 2005 Backups How to change table owner when restoring database? RE: How to change table owner when restoring database?

  • September 14, 2010 at 3:09 pm

    #1221017

    assh1122 (9/14/2010)

    Hi,

    Thank you both for taking your time to answer my question. I could able to successfully change the table owner using the sp_changeobjectowner '[currentschema].[currenttable]' , 'dbo' command. Now I have to change the owner of more than 1000 tables. I am thinking of creating a stored procedure to iterate the sp_changeobjectowner command n no of times to make the owner change. I want to know if it is the right approach? I am new to SQL programming. Please let me know if there is any another approach to accomplish this? Your help is greatly appreciated.

    There's an undocumented system stored procedure you can use:

    execute sp_msforeachtable 'sp_changeobjectowner ''?'' , ''dbo'';print ''?'';'

    The ? is replaced with the schema/name of the table, in the [] brackets. Those are all single-quotes there also... you need to double them up when using within a string.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2