Change table owner to dbo
-
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.
-
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
-
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 -
It is possible. One way would be if he explicitly created the tables with an owner name.
Andy
-
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."
-
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.
-
Yes, all tables were created explicitly with owner name.
And how can I replace the owner name with 'dbo'
-
For the second param all you need is dbo, not dbo.t1.
Andy
-
Thanks Andy.
This statement works:
EXEC sp_changeobjectowner 'Andrew.T1', 'dbo'
Thanks alot... Ang
-
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.
-
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
-
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
-
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
, @newowner='dbo' "Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply