Object has 2 owners

Question

Post reply

Object has 2 owners

Frankie-464050

Ten Centuries

Points: 1010
More actions
February 25, 2011 at 3:46 pm

#247440

Have a stored procedure that has two schema owners and I need to remove one.
The alter schema statement doesn't work because of the 2 records that exist in sys.objects. Thinks it's just fine because dbo does own it. Is there a way to drop a sys.objects record.
I'm thinking even if I could alter schema with the correct object_id it would fail because it would create a duplicate in sys.objects (same object, same owner)
No sure how this happened but it's a mess. The non dbo schema owner is an orphaned user and for some reason even if I change the owner of the schema to dbo the object is still owned in sys.objects by the former owner.
??? :w00t:
Any help would be appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

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

MADHAN_PERS SSC Enthusiast Points: 105 More actions · Answer 1

Hi

please try to use sp_changeobjectowner and see whether it works.

If possible could you please share the db script with all objects so that i can try it at my end.

Frankie-464050 Ten Centuries Points: 1010 More actions · Answer 2

Tried the suggestion still does not work because it's based on NAME not object_id

Here's a better synopsis of the issue:

Have a database SQL 2008 compatibility level SQL 2000

Have a schema that the login had been dropped, that orphaned schema owns a proc.

Now here's the issue, another schema (dbo) also owns the proc so I have 2 records for the same proc in sys.objects

nameobject_id schema_id

by_LSN 1990598451

by_LSN 5190609856

Any thoughts on how to get rid of a duplicate object? It's the one where schema_id is 6. Can't find any command to change or drop by object_id. When I do the drop transfer etc commands it's just not touching the one I want to change

Even when I delete the object , it still has a record in sys.objects for the orphaned schema_id and when I try to do an alter schema transfer I get the following error

Msg 15151, Level 16, State 1, Line 1

Cannot find the object 'by_LSN', because it does not exist or you do not have permission.

any ideas?

Lowell SSC Guru Points: 323518 More actions · Answer 3

what does this return?

SELECT

schema_name(6) As schemaName,

object_name(519060985) As Objectname,

'EXEC sp_changeobjectowner '''

+ QUOTENAME(schema_name(6))

+ '.'

+ QUOTENAME(object_name(519060985)) + ''''

AS PotentialCommand

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Frankie-464050 Ten Centuries Points: 1010 More actions · Answer 4

It returns:

schemaName = Domain\Username

Objectname =by_LSN

PotentialCommand =

EXEC sp_changeobjectowner '[Domain\Username].[by_LSN]'

Also tried

Drop proc [Domain\Username].by_LSN

Again get not exist or permission error: Logged onto SQL as sa and then tried my domain admin log in... same errors

Frankie-464050 Ten Centuries Points: 1010 More actions · Answer 5

Yipieee!!!!

I just got it. On the drop proc had to have the [] around both the schema AND the proc name.

drop proc [Domain\Username].[by_LSN]

Thanks all for your time and effort ... this one had me stumped.