Post reply

Object has 2 owners

  • 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.

  • February 25, 2011 at 4:43 pm

    #1291837

    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.

  • March 1, 2011 at 10:01 am

    #1293056

    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

    name object_id schema_id

    by_LSN 199059845 1

    by_LSN 519060985 6

    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?

  • March 1, 2011 at 11:48 am

    #1293110

    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!

  • March 1, 2011 at 11:57 am

    #1293116

    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

  • March 1, 2011 at 11:59 am

    #1293122

    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.

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

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