By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,091 Members | 1,546 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,091 IT Pros & Developers. It's quick & easy.

T-Sql for changing the Owner of the table objects in a database

P: 1
I have a MS SQL Server Database which consists of several Tables, amongh some has owner as "dbo" and some has another owner like "xyz".
Is there any T-SQL that can change the owner [xyz] of the tables to "dbo".

And plz clarify the fact how a owner can be determined at the time of table creation.
Feb 6 '07 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 1,017
To change owner

EXEC sp_changeobjectowner 'table_name', 'dbo'

To create table or any database object with dbo owner you have to mention it in create statement otherwise it is your UID.

[PHP]Create table dbo.table_name ( column1 datatype1, .... )[/PHP]

I think it depends on Server settings in some cases dbo is a default owner.
Feb 6 '07 #2

Expert 100+
P: 168
I think it depends on Server settings in some cases dbo is a default owner.
It depends on current user, so if user that executes the create statement is dbo in the database, then objects by default would be created under dbo
Feb 6 '07 #3

P: 1

You can change object owner in a streatch with the following command if you are having more no of tables to change object owner.

select 'execute sp_changeobjectowner '+ '''' + 'xyz.'+name+''''+','+'''dbo''' from sysobjects where xtype = 'U' and uid=5 order by name
Feb 15 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.