Post reply

Objects ownership in SQL2000

  • December 4, 2009 at 11:53 am

    #94219

    Hello,

    We have SQL 2000 and SQL 2005.

    I have created a login called "A" using windows authentication and assigned DBO role to the login. When login A create any procedure in SQL 2005 and the owner of the object sets to DBO. But samething if we do in SQL 2000, the object owner set as "A". It does not go as DBO owner. If we give Sysadmin server role to the login A, the object created as DBO. Is it bug with SQL 2000 or is there any other workaround to solve this problem.

    Thanks.

  • December 4, 2009 at 12:33 pm

    #1087970

    Do you mean it's a member of the db_owner role?

    If that's the case, you must specify the owner during object creation. For instance:

    CREATE TABLE dbo.Foo (Moo int);

    GO

    K. Brian Kelley
    @kbriankelley

  • December 4, 2009 at 12:40 pm

    #1087975

    Thanks.. It's for the deployment purpose. Sometime the developer does not specify the prefix as dbo. I would like to handle this kind of scenario.

    Thanks.

  • December 5, 2009 at 10:15 am

    #1088176

    Then you will need to use sp_changeobjectowner to change the ownership of the object, if that's the case. A simple script will do this. Something like this should build the appropriate commands to execute:

    SELECT 'EXEC sp_changeobjectowner ''[' + USER_NAME(uid) + '].[' + name + ']'', ''dbo'';'

    FROM sysobjects

    WHERE uid <> USER_ID('dbo');

    K. Brian Kelley
    @kbriankelley

  • December 5, 2009 at 7:14 pm

    #1088224

    Or do a simple search of scripts built by the developer. If they can't remember to add dbo., perhaps you can reject their scripts until they do

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

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