Change table owner to dbo

Question

Change table owner to dbo

AngPeng

SSC-Addicted

Points: 489
More actions
August 30, 2002 at 3:25 pm

#63954

Hi,
I have a Database that was created by a sysadmin "Andrew". All the table names were
created as Andrew.T1, Andrew.T2,...
How can I change Andrew.T1, Andrew.T2,...
to
dbo.T1, dbo.T2,... and keep Andrew as original owner of the database.
I'm looking to use this select statement
"Select * from dbo.T1" or
"Select * from T1"
instead of
"Select * from Andrew.T1"
Thank you for you help.

Viewing 13 posts - 1 through 13 (of 13 total)

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

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

The table object has an owner property that is read write. If you want to change it using just pure TSQL use sp_changeobjectowner.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
Connect with me on LinkedIn

Prakash Heda SSCertifiable Points: 5539 More actions · Answer 2

A confusion with the questions....

I am sure as sysadmin if a table is created it will always be create as dbo of the database. So what are u saying is possible only if Andrew is not sysadmin........

If what I said is true than u have to use this for each table create by andrew...

EXEC sp_changeobjectowner 'authors', 'ficuser'

Cheers,

Prakash

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

Andy Warren SSC Guru Points: 119902 More actions · Answer 3

It is possible. One way would be if he explicitly created the tables with an owner name.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
Connect with me on LinkedIn

AngPeng SSC-Addicted Points: 489 More actions · Answer 4

The table was created with the owner's name.

To select the content of the table, I need to provide the owner's name.

"Select * from Andrew.T1"

Otherwise, if I use

"Select * from T1"

I will get an error "Invalid object name 'T1'

I tried this sp_changeobjectowner and I also get an error.

EXEC "sp_changeobjectowner 'Andrew.T1', 'dbo.T1'"

The Error is "User or role 'dbo.T1' does not exist in this database."

Justin-14543 SSCrazy Points: 2017 More actions · Answer 5

He might have logged in as Andrew at the time he created the tables Or he might have created the tables explicitly with owner name as Andrew.

Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

AngPeng SSC-Addicted Points: 489 More actions · Answer 6

Yes, all tables were created explicitly with owner name.

And how can I replace the owner name with 'dbo'

Andy Warren SSC Guru Points: 119902 More actions · Answer 7

For the second param all you need is dbo, not dbo.t1.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
Connect with me on LinkedIn

AngPeng SSC-Addicted Points: 489 More actions · Answer 8

Thanks Andy.

This statement works:

EXEC sp_changeobjectowner 'Andrew.T1', 'dbo'

Thanks alot... Ang

caneja SSC Journeyman Points: 93 More actions · Answer 9

exec sp_msforeachtable " sp_changeobjectowner @objname= , @newowner='dbo' "

This one has saved me a ton of time. It will do the whole DB at once.

AngPeng SSC-Addicted Points: 489 More actions · Answer 10

Hi ceneja,

I am unable to find 'sp_msforeachtable' in the books online.

Can you give me a little more detail on your script? or your stored procedure?

Thanks...Ang

Andy Warren SSC Guru Points: 119902 More actions · Answer 11

Its undocumented, officially anyway. It along with the foreachdb are procs provided by MS that basically hide a cursor that cycles through all the tables (or databases) to run a command against each. Handy at times! Brian K has articles on both here on the site.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
Connect with me on LinkedIn

AngPeng SSC-Addicted Points: 489 More actions · Answer 12

Thank you all for helping me out.

This script works for me:

exec sp_msforeachtable "sp_changeobjectowner '?' ,'dbo'"

And thank you Brian K for your article on msforeachtable.

Regards... Ang