Object not belong to DBO

[jluost1]

I restored a database from a backup file on the other server. Then I ran sp_auto_fix_login to fix the logins on the new server.

I need to run tens of stored procedures (with a single call) developed by our vendor and all the procedures and other referrenced objects have [dbo] as prefix([dbo].objectname). But when I tried to get object_id([dbo].objectname), I got null. When I tried to run &quot;SELECT OBJECT_ID(<b>[mylogin]</b>.objectname)&quot;, I got the object_id. Here &quot;mylogin&quot; is one user account that created the database originally. mylogin belongs to sysadmin server role and is dbowner.

How can I run all the procedures without changing all &quot;dbo&quot; to &quot;mylogin&quot; in all the places?

By the way, it's SQL7 on NT4. I connected to SQL7 using Query Analyzer with sa account.

 

[MeanGreen]

So are you saying all objects are dbo except one stored procedure, or are all objects owned by your login? If only the one object is owned by you, log into SQL Analyzer as yourself, and since you have dbo rights, you should be able to get to the other stored procedures. If all objects are owned by you, same thing, log in as yourself and run everything. Last alternative is change ownership of object(s) to the dbo. You can do this with a cursor to loop through each object and change ownership. Very easy to do if you have adminstrator rights.

Hope this helps.

 

[osjohnm]

If its only one object then execute sp_changeobjectowner

eg:

exec sp_changeobjectowner 'yourlogin'.object, 'dbo'

if you use a domain account then change it like so.

exec sp_changeobjectowner [domain\login].object, 'dbo'.

hope this helps

John

 

[jluost1]

Thank you both.

I connect QA with &quot;mylogin&quot; account, which is DB_owner and sysadmin. Under myDatabase, I ran the following:

sp_changedbowner [mylogin], [dbo]

Then I got the following error:

&quot;The proposed new database owner is already a user in the database.&quot;

When I checked the database owner in Enterprise Manager, dbo is the owner of myDatabase.

To make it simple, I would like the following to work but it is not now:

select object_id(N'dbo.myTable')

However, the following works:

select object_id(N'mylogin.myTable')

What do I need to do?

Thanks.

 

[MeanGreen]

Try this:

EXEC sp_changeobjectowner 'myTable', 'dbo'


Hope this helps.

 

[osjohnm]

Hi

My first post explained how to change only the owner of the table. The owner of the database is fine since all your other tables and stored procedures are owned by dbo.

Here is the code again:

--basic
exec sp_changeobjectowner 'tablename', 'dbo'

-- that odesn't work then specify the owner of the table(your login) as well as the table name. This is for a sql login
exec sp_changeobjectowner 'yourlogin.table', 'dbo'
-- if you use nt authentication then this would work
exec sp_changeobjectowner '[domain\login].object', 'dbo'

You will recieve the following warning but you can ignore it:
Caution: Changing any part of an object name could break scripts and stored procedures.

Hope you come right

John

 

[jluost1]

Thank you. That works.

But I don't understand why I need to do this. Doing this way requires me to do it on all the objects (tables and SPs). That's a pain.

So, two more questions:

(1) I connect QA as myLogin(sysadmin and db_owner), all objects are owned by myLogin. Since myLogin is sysadmin, all objects belong to myLogin should belong to dbo also. Why is it not the case?

(2) If I have to change object owner for all tables and stored procedures, is there a way to do it programmatically?

Thank you again.

 

[MeanGreen]

Here is a cursor routine that should work for you:

declare @sqlstr varchar(1500)
declare @objname varchar(25)
declare @oldowner varchar(25)
declare @newowner varchar(25)

select @oldowner = 'OLDOwner'
--Remember that the newowner must exist within the database
select @newowner = 'dbo'
select @sqlstr = ''
declare loop_var cursor for
select name from sysobjects
where user_name(uid) = @oldowner

open loop_var
fetch next from loop_var into @objname

while @@fetch_status = 0
begin
select @sqlstr = 'sp_changeobjectowner ''' + @objname + ''',''' + @newowner + ''''
exec (@sqlstr)
select @sqlstr = ''
fetch next from loop_var into @objname
end
close loop_var
deallocate loop_var


Hope this helps.