Post reply

How to change table owner when restoring database?

  • September 13, 2010 at 7:47 pm

    #226204

    Hi All,

    I am using SQL Server 2005. I have taken backup of a sample database using SQL Server management studio. I want to restore it into a new database with different table owner. Could anyone please let me know how can I do this in enterprise manger? Is there any option in SQL Server management studio to do this? Any help on this is greatly appreciated.

  • September 13, 2010 at 9:35 pm

    #1220419

    For each object in the database

    sp_changeobjectowner '[currentschema].[currenttable]' , 'dbo'

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 13, 2010 at 9:36 pm

    #1220421

    You can't do it in the restore. You would restore first, then use Jason's code to change the owner.

  • September 13, 2010 at 9:47 pm

    #1220424

    Thanks for clarifying that Steve. I meant to say that.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 14, 2010 at 11:12 am

    #1220855

    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.

  • September 14, 2010 at 11:27 am

    #1220865

    Choose "Results to Text", then copy/paste the results into a new window.

    declare @OldOwner varchar(100), @NewOwner varchar(100)

    set @OldOwner = 'oldowner'

    set @NewOwner = 'dbo'

    select 'sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + '''

    go'

    from information_schema.tables where Table_schema = @OldOwner

    You can skip the variables and hardcode the owner names in the script.

  • September 14, 2010 at 2:12 pm

    #1220969

    homebrew01 (9/14/2010)

    Choose "Results to Text", then copy/paste the results into a new window.

    declare @OldOwner varchar(100), @NewOwner varchar(100)

    set @OldOwner = 'oldowner'

    set @NewOwner = 'dbo'

    select 'sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + '''

    go'

    from information_schema.tables where Table_schema = @OldOwner

    You can skip the variables and hardcode the owner names in the script.

    This method should work for you. I recommend checking all of the records returned by the query - sanity check.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • 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

  • September 14, 2010 at 4:58 pm

    #1221065

    Hi,

    Thank you very much for your response.The following Script worked absolutely fine.

    Declare @OldOwner varchar(100), @NewOwner varchar(100)

    set @OldOwner = 'dbo'

    set @NewOwner = 'sys'

    select 'sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + '''

    go'

    from information_schema.tables where Table_schema = @OldOwner

    I am new to SQL programming. Could you please suggest me any online tutorial or link to learn the script you have used here? I greatly appreciate your help.

  • September 14, 2010 at 7:16 pm

    #1221088

    WayneS (9/14/2010)

    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.

    My caution against using that method is that it will execute the script (as is above) prior to being able to confirm that the desired tables and only the desired tables to be changed will be changed.

    It's nice to have that sanity check.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 14, 2010 at 11:55 pm

    #1221127

    assh1122 (9/14/2010)

    Hi,

    I am new to SQL programming. Could you please suggest me any online tutorial or link to learn the script you have used here? I greatly appreciate your help.

    Search for information on "Dynamic SQL"

  • September 15, 2010 at 7:11 am

    #1221302

    CirquedeSQLeil (9/14/2010)

    WayneS (9/14/2010)

    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.

    My caution against using that method is that it will execute the script (as is above) prior to being able to confirm that the desired tables and only the desired tables to be changed will be changed.

    It's nice to have that sanity check.

    Good point Jason.

    Even if you break the statement down, and do just a :

    execute sp_msforeachtable 'print ''?'';'

    to see which tables you're processing, if you end up with more than what you want then you would still need to resort to the other method to week them out.

    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

  • September 15, 2010 at 9:16 am

    #1221425

    WayneS (9/15/2010)

    CirquedeSQLeil (9/14/2010)

    WayneS (9/14/2010)

    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.

    My caution against using that method is that it will execute the script (as is above) prior to being able to confirm that the desired tables and only the desired tables to be changed will be changed.

    It's nice to have that sanity check.

    Good point Jason.

    Even if you break the statement down, and do just a :

    execute sp_msforeachtable 'print ''?'';'

    to see which tables you're processing, if you end up with more than what you want then you would still need to resort to the other method to week them out.

    Yes - that would be fine.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 15, 2010 at 1:23 pm

    #1221628

    Thank you for the guidance.

  • September 15, 2010 at 4:21 pm

    #1221767

    You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 14 (of 14 total)

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