Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    5

    Smile Unanswered: Database ownership

    Hi

    Can an NT GROUP own a database in SQL2000 ? I'm trying to have users in the group create objects with dbo owner ship by default.

    However sp_changedbowner does not seem to recogise the group as a login even though sp_helplogins lists it .

    Thanks

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    may be a bug with sp_changedbowner, when done through EM it works.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2003
    Posts
    5
    Originally posted by Paul Young
    may be a bug with sp_changedbowner, when done through EM it works.
    Thanks Paul.

    I can't seem to see where to do this in EM. Do you mean by adding the group to the dbo role?

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    yup!
    under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jan 2003
    Posts
    5
    Originally posted by Paul Young
    yup!
    under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.
    Thanks again

    However I'm getting tables owned by individual nt users which cannot be seen by other members of that group (i'm testing with users
    connecting via query analyzer using nt authentication.

    What I'm looking for is to be able to get the database owned by
    the nt group so that any table created by the users are owned by dbo
    and not the individual users, or at least have it so that all users of the group can see and manipulate the tables.

    Any ideas?

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.

    Can you move all the user objects to a holding db and try your tests again?
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Jan 2003
    Posts
    5
    Originally posted by Paul Young
    user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.

    Can you move all the user objects to a holding db and try your tests again?
    Thanks for your patience. When a sql login owns a database then
    all tables created are dbo.table rather than sqllogin.table

    This is what I'm trying to achieve with the NT group .

    When I create a table under an nt user which is in that nt group (whose sql login has dbo access to the database) the table is created as ntuser.table , and that nt user appears as a user in the database (where it didn't appear before ).

    I tried to run the sp_addalias to get around this too but this also gave me an error as if it didn't recognise the nt group sql login.

    I think I'm on the right track its just annoying that I can't find any real information about this scenario. My other option is to ensure that all tables are explicitly created with dbo. but not all 3rd party applications are so forgiving .


    Thanks again for your help Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •