471,571 Members | 1,002 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,571 software developers and data experts.

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

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
3 16378
iburyak
1,017 Expert 512MB
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
almaz
168 Expert 100+
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
Hi,

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.

Similar topics

reply views Thread by Cornelio Royer | last post: by
12 posts views Thread by Serman D. | last post: by
alisyah
9 posts views Thread by alisyah | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.