Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to change table owner when restoring database? Expand / Collapse
Author
Message
Posted Monday, September 13, 2010 7:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 15, 2010 1:21 PM
Points: 9, Visits: 45
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.
Post #985190
Posted Monday, September 13, 2010 9:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:11 PM
Points: 20,455, Visits: 14,075
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #985204
Posted Monday, September 13, 2010 9:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:49 PM
Points: 32,768, Visits: 14,929
You can't do it in the restore. You would restore first, then use Jason's code to change the owner.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #985206
Posted Monday, September 13, 2010 9:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:11 PM
Points: 20,455, Visits: 14,075
Thanks for clarifying that Steve. I meant to say that.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #985210
Posted Tuesday, September 14, 2010 11:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 15, 2010 1:21 PM
Points: 9, Visits: 45
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.


Post #985715
Posted Tuesday, September 14, 2010 11:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
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.



Post #985728
Posted Tuesday, September 14, 2010 2:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:11 PM
Points: 20,455, Visits: 14,075
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #985846
Posted Tuesday, September 14, 2010 3:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 6,544, Visits: 8,754
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #985900
Posted Tuesday, September 14, 2010 4:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 15, 2010 1:21 PM
Points: 9, Visits: 45
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.
Post #985953
Posted Tuesday, September 14, 2010 7:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:11 PM
Points: 20,455, Visits: 14,075
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #985982
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse