[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1139
  • Last Modified:

Change Object owners

I have found quite a bit about chaning object owners in SQL, however, I'm not having any luck with it.  I'm REALLY NEW to SQL.  Could someone tell me what I'm doing wrong?  

use Track6_ContactSQL
go
exec sp_changeobjectowner 'elliott', 'dbo'
go

Results are:  
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'elliott' does not exist or is not a valid object for this operation.

Elliott IS still the owner of these tables.  HELP!!!

Thanks
0
SCS1ST
Asked:
SCS1ST
  • 12
  • 12
  • +2
1 Solution
 
ptjcbCommented:
exec sp_changeobjectowner 'elliott', 'dbo'

You were close

exec sp_changeobjectowner 'table name', 'dbo'

FROM BOL:
sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'
0
 
ptjcbCommented:
You were trying to change the object owner of Elliott to dbo and SQL Server complained since there is no object elliott.
0
 
akshah123Commented:
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?
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
SCS1STAuthor Commented:
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?
0
 
ptjcbCommented:
exec sp_changeobjectowner 'dbo.A_B_Credit_Report', 'dbo'
0
 
ptjcbCommented:
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?
0
 
SCS1STAuthor Commented:
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?
0
 
SCS1STAuthor Commented:
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.  
0
 
ptjcbCommented:
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'

0
 
ptjcbCommented:
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.
0
 
SCS1STAuthor Commented:
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?
0
 
SCS1STAuthor Commented:
Oh, just got your message.  That's a possible solution.  Is it hard to register and reregister?
0
 
ptjcbCommented:
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.
0
 
SCS1STAuthor Commented:
Here's the results:  
Server: Msg 208, Level 16, State 3, Line 1
Invalid object name 'dbo._qryStaff_Active_LoanOfficer'.
0
 
SCS1STAuthor Commented:
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.
0
 
SCS1STAuthor Commented:
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
0
 
ptjcbCommented:
You are right - EM allows register/unregister. QA does not. I've been using Management Studio since November.
0
 
SCS1STAuthor Commented:
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?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
imran_fastCommented:
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
0
 
ptjcbCommented:
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.

0
 
ptjcbCommented:
Good morning...ok, let's look at the issues one at a time.

You have tables created by eliott that you want to change to dbo. We used sp_changeobjectowner and solved that. What I should have offered is this:

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
  @old = 'oldOwner_CHANGE_THIS'
  , @new = 'dbo'
  , @sql = '
  IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
  WHERE
      QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
      AND TABLE_SCHEMA = ''' + @old + '''
  )
  EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''

EXECUTE sp_MSforeachtable @sql

***
This is from David Penton a Microsoft MVP for SQL Server. The link is here: http://weblogs.asp.net/owscott/archive/2004/01/30/65229.aspx

I was under the impression that you had only one or two tables with eliott - it sounds as if you have many more than that. This script changes all of the ones that are not 'dbo' to dbo. I'm not sure if that is something you want or not. I have worked in shops where that was how tables/stored procedures were developed by first using the developer's name and then, when it went into production changing it to dbo.

I should mention that when you move to SQL 2005 one of the "new things" is that the schema name means something. We finally move away from the dbo owns everything approach.

Two - QA does not refresh

That is odd. I have not used 2000 for five months now, but I do not remember having issues like that. Even closing/re-opening QA did not solve it. I'll do some more research on that.
0
 
SCS1STAuthor Commented:
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.  
0
 
ptjcbCommented:
Thank you for the kind words. Laughing, it seemed like an easy question when we started....
0
 
SCS1STAuthor Commented:
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?  
0
 
ptjcbCommented:
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
0
 
SCS1STAuthor Commented:
THANK YOU!  THANK YOU!  THANK YOU!  
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 12
  • 12
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now