Change Object owners

exec sp_changeobjectowner 'elliott', 'dbo'

You were close

exec sp_changeobjectowner 'table name', 'dbo'

FROM BOL:
sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

You were trying to change the object owner of Elliott to dbo and SQL Server complained since there is no object elliott.

Well,

the way

exec sp_changeobjectowner

works is that the first object is the name of the object that you want the owner to change and not the name of the current owner.  

Thus, if you want to change owner of a table called 'table1' you would do

exec sp_changeobjectowner 'table1', 'dbo'

Does that make sense?

Ok, I see now where I went wrong.  However, when I use the table name I get the following error:
Server: Msg 2809, Level 18, State 1, Line 1
The request for procedure 'dbo_A_B_Credit_Report' failed because 'dbo_A_B_Credit_Report' is a table object.
 I don't see another table with that name.  

Is it strange that I might see the dbo.tablename in the QA window but not in the Server Enterprise?

exec sp_changeobjectowner 'dbo.A_B_Credit_Report', 'dbo'

exec sp_changeobjectowner 'A_B_Credit_Report', 'dbo'

Sorry - dbo.A_B_Credit_Report would happen after you change the owner. Is this the command that you used before?

Yes, I think it was.  I didn't type in the table name.  I just drug it in from the left side.  Should I not do that?

Why do I see tables in the Object Browser of the Query Analyzer that I no longer see in the Server Enterprise?  For example, there were several tables that were duplicate just with different owners.  I've deleted the duped tables in  Server Enterprise, and have given some the 'dbo." owner.  Yet, in the Object Browser I still see all the original tables.  I've tried to referesh, shut down and reopened each program and I still see the in the OB of QA.  

I think when you drag it that it automatically adds the schema name. I'm not sure if the name of the table is dbo_A_B_Credit_Report or what.

exec sp_changeobjectowner 'olduser. + TABLE_NAME+' , 'dbo'

so maybe: exec sp_changeobjectowner 'elliott.dbo_a_b_credit_report', 'dbo'

QA issues - I'm not sure why you would see "ghost" tables, especially after you have refreshed the database. You may have to unregister and re-register the database if they do not clear themselves.

Yes, it does automatically add the schema name and it seems to work when there's not a table object of another table with the "dbo" already an owner.  What's throwing me is I keep getting these errors:

Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 63
Cannot change owner of object 'elliott._qryStaff_Active_LoanOfficer' or one of its child objects because the new owner 'dbo' already has an object with the same name.

And I've searched and searched and it's really not there.  Does SQL hide tables?

Oh, just got your message.  That's a possible solution.  Is it hard to register and reregister?

True - it will not let you duplicate table names. You will have to delete either the old dbo table or the elliott table.

In QA - SELECT * FROM dbo.._qryStaff_Active_LoanOfficer
What results are returned?

Find the database name in QA, right-click. You should see the option to unreqister. Then you'll have to right click on the server name to re-register the database.

Here's the results:  
Server: Msg 208, Level 16, State 3, Line 1
Invalid object name 'dbo._qryStaff_Active_LoanOfficer'.

Yet, I still get this error msg when I try to change owner names:

Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 63
Cannot change owner of object 'elliott._qryStaff_Active_LoanOfficer' or one of its child objects because the new owner 'dbo' already has an object with the same name.

When I rt click on the database name in QA I don't get an option to unregister.  I'm running version 8 of SQL.  Is there somewhere else I'd find it?  I've been searching, but no luck

You are right - EM allows register/unregister. QA does not. I've been using Management Studio since November.

I don't mean to sound so stupid about SQL, but I did say I was new to this.  Is Detach the same as unregister?  I don't see unregister in EM either.  Closest would be detach or take offline?    And is this comperable to a compact & repair in MS Access?

SCS1ST,
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 63

select * from information_schema.tables where table_name ='qryStaff_Active_LoanOfficer'

then chech how many rows it returns ? The table_Schema in the result set specifies the object owner

run this query
=========

select uid, name from sysobjects where xtype= 'u' and name like ='%qryStaff_Active_LoanOfficer%'

you will find out that how many tables you have with different owner

It's ok - we all started somewhere. No. Detach/Attach are very different. These commands allow you to unattach a database from one server, move it to a new server and then attach it to that server.

aneeshattingal  Your statment worked and gave me the following results which is what I would have predicted.  However, if Elliott has the only table with that name why won't it let me change the owner?  

ClientTrack6_ContactSQL elliott      _qryStaff_Active_LoanOfficer      BASE TABLE      


ptjcb
I got very excited when I saw and tried the script you send from David Penton a Microsoft MVP for SQL Server.  I didn't get any errors and it said that it was successful.  However I still have seven table that are still under Elliott.  So I'm trying to change them one at a time with:
Ok after playing with the scripts I managed to get 5 out of the seven switched to the dbo.  However, these last three tables just won't switch.  The way I managed with the others is one time I'd use "elliot." before the table name, receive an error, then I'd take it off, receive an error, then put it back on again and it would work.  Don't ask me why this worked.  But it did.  Just not with these last three.  
exec sp_changeobjectowner 'elliott._qryStaff_Active_LoanOfficer',dbo
ERROR:  Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 63
Cannot change owner of object 'elliott._qryStaff_Active_LoanOfficer' or one of its child objects because the new owner 'dbo' already has an object with the same name.
____________________________________________________________________________________
exec sp_changeobjectowner 'elliott.dbo_C_Collection_Notes Query',dbo
Error:  Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 63
Cannot change owner of object 'elliott.dbo_C_Collection_Notes Query' or one of its child objects because the new owner 'dbo' already has an object with the same name.

After further looking into the database I found a function and a view with those exact same names.  So I guess I'll rename the function and views and try again.  

I cannot thank everyone enough for all your help through this.  I've learned a lot.  Thanks so much.  
ptjcb:  You are my first experience on EE where someone stayed with me through to final answers and was SO patient, understanding and devoted.  Thank you so much for all of your devotion.  You definately deserve this A.  

Thank you for the kind words. Laughing, it seemed like an easy question when we started....

I have another question about the same thing.  Sorry.  I was able to change the view name and get the owner of the collection notes table to DBO, but not the staff active loanofficer.  It won't let me rename the function.  Still working on that.  My question is that in doing this I noticed that a lot of functions, views & SPs are also under elliott.  Is there a way to modify the script you sent me to do views, functions & SPs instead of tables?  

No problem - this is from an earlier answer from aneeshattingal (we were answering another question about changing the owner). This will change your stored procs.

DECLARE @dbObject VARCHAR(255)
DECLARE CurDbObjects CURsoR FOR
SELECT su.NAME + '.' + so.NAME AS OBJECT
FROM sysobjects so , sysusers su
WHERE so.UID = su.UID AND su.NAME <> 'dbo'
AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE = 'P'ORDER BY so.NAME

OPEN CurDbObjects
FETCH NEXT FROM CurDbObjects INTO @dbObject
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('sp_changeobjectowner ''' + @dbObject + ''' , ''dbo''')
FETCH NEXT FROM CurDbObjects INTO @dbObject
END
CLOSE CurDbObjects
DEALLOCATE CurDbObjects

To change it for functions you have to change this line to read:

AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE = 'FN'ORDER BY so.NAME

To change it for views you change it to:

AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE = 'V'ORDER BY so.NAME

THANK YOU!  THANK YOU!  THANK YOU!