Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    229

    Unanswered: Best practice for 'dbo'

    When setting up databases for end users, what's the best practice regarding who's the dbo for each individual database - the user itself or a sysadmin?

    Does it really have any importance at all who the owner (as defined by 'dbo') is ?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd strongly recommend leaving sa as dbo, and if need be then making the user a member of the db_owner role if you need that.

    -PatP

  3. #3
    Join Date
    Jul 2002
    Posts
    229
    Thanks.

    The issue was raised when I noticed that for older user databases, someone had assigned a system admin as the dbo by his own, personal user name. When than person then left, and his user was removed, those user databases became orphans.

  4. #4
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    You can assign db_ddladmin.

    db_ddladmin act same as dbo but it has limited rights comparing db_owner.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I suspect that Coolberg's problem wasn't one of permission level (they want the user to be equivalent to dbo), but one of ownership (they don't want the login to "own" the database).

    There are two issues here that are tightly intertwined, and often confused.

    A login is what gives a person access to SQL Server. Logins exist at the server level, and can be either SQL Authenticated or Windows Authenticated. Logins are what "own" a database.

    A User is what gives a person permissions inside a SQL Server database. Users exist only inside a database, and are logically tied to exactly one login on the server.

    I think that Coolberg wants to keep the ownership of the database limited to an administrative login. I strongly recommend using sa (because you just about can't delete that login), but I agree with the general idea regardless of what login you use.

    By using this strategy, you can keep the database ownership limited to an administrative login, but still make any database users memebers of the db_owner role (giving them exactly the same permissions as dbo).

    -PatP

  6. #6
    Join Date
    Jul 2002
    Posts
    229
    Thanks.
    Yes, I'll go for the sa user.
    My main goal is to avoid getting orphanized databases when users are leaving in the future.

Posting Permissions

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