Post reply

sp_changeobjectowner problems...

  • September 13, 2005 at 6:51 am

    #102789

    I am trying to change ownership of a few tables from Windows NT logins to DBO. I have done this many time before so confidentely I types

    sp_changeobjectowner 'Loan_Dim', 'dbo'

    To my surprise an error displayed:

    Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38

    Object 'Loan_Dim' does not exist or is not a valid object for this operation.

    So I opened this procedure and checked the part where the error was generated. I copied this check on whether the object may have an ownershipchange and ran it like this:

    if (277576027 is null) OR

      (select parent_obj from sysobjects where id = 293576084) <> 0 OR

      ObjectProperty(293576084, 'IsMSShipped') = 1 OR

      ObjectProperty(293576084, 'IsSystemTable') = 1 OR

      ObjectProperty(293576084, 'ownerid') in (0,3,4) OR --public, INFORMATION_SCHEMA, system_function_schema

      -- Check for Dependencies: No RENAME or CHANGEOWNER of OBJECT when exists:

      EXISTS (SELECT * FROM sysdepends d WHERE

       d.depid = 293576084  -- A dependency on this object

       AND d.deptype > 0  -- that is enforced

       AND 293576084 <> d.id  -- that isn't a self-reference (self-references don't use object name)

       AND 293576084 <>   -- And isn't a reference from a child object (also don't use object name)

        (SELECT o.parent_obj FROM sysobjects o WHERE o.id = d.id)

      &nbsp

    BEGIN

     PRINT 'A-OK!'

    END

    ELSE 

     PRINT 'Somethings wrong!'

    Result is 'Somethings wrong!'  Then I checked each line seperately, like this:

    select ObjectProperty(133575514, 'IsMSShipped')

    select ObjectProperty(133575514, 'IsSystemTable')

    select ObjectProperty(133575514, 'ownerid')

    select parent_obj from sysobjects where id = 133575514 -- IN(0,3,4)

    SELECT * FROM sysdepends d WHERE

       d.depid = 133575514  -- A dependency on this object

       AND d.deptype > 0  -- that is enforced

       AND 133575514 <> d.id  -- that isn't a self-reference (self-references don't use object name)

       AND 133575514 <>   -- And isn't a reference from a child object (also don't use object name)

        (SELECT o.parent_obj FROM sysobjects o WHERE o.id = d.id)

    Every result was valid for the expected ‘A_OK!’

    I am flabbergasted. I have googled the internet, but could not find any info on something being wrong with sp_changeobjectowner. The tables concerned are not related with other tables, are not systemtables and are not part of a replication. They are not self-referencing and-so-on. I can easily drop and recreate the tables, it’s all in a developmentserver, but I really would like to know what I am missing here.

     

    TIA,

     

    Greetz,
    Hans Brouwer

  • September 13, 2005 at 7:57 am

    #589363

    Have you tried sp_changeobjectowner 'oldownerName.Loan_Dim', 'dbo' ?

     

    /Kenneth

  • September 14, 2005 at 5:06 am

    #589640

    Tnx for the response, Kenneth. I had not tried a qualified reference, but have just now. Result is the same.

    I'll just have to dig deeper, I guess.

    Greetz,
    Hans Brouwer

  • September 16, 2005 at 3:56 am

    #590475

    ..and you can do a 'SELECT * from oldowner.Loan_Dim' as well...?

    /Kenneth

  • September 16, 2005 at 5:36 am

    #590508

    Got it! Had to place quotes round the qualified tablename:

    sp_changeobjectowner 'europe\stan.loan_dim', 'dbo'

    Thanks for the help.

    Greetz,
    Hans Brouwer

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

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