db owner best practice

  • When we create a database, the owner is usually is whoever created the database.

    I read some suggested right after create the db, change its owner to sa.

    Some said no, for SA is an account that have super permissions.

    I wonder what is the best practice?

  • I like using sa as the database owner. Since sa already has all permissions, you can't accidentally give extra permissions by doing so. It's not escalating any privileges, it's not allowing anyone to log in or execute as sa that couldn't before. It's just saying that this database is owned by the built-in sysadmin account

    And it means that you can't end up with an owner-less database if the creating AD account is removed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I'll strongly second Gail's response.  I'm thinking that Brent Ozar would, as well.  His sp_Blitz tool checks to see if the database is owned by "SA" or not and makes a recommendation to do so if it is not.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I have an SQL Agent job that checks the db owner and changes it to sa if it is anything else.

    ...

  • I would make one that reports when the owner is something else but I'm not sure I'd make one do an auto-change.  You just can't tell when that could break an application, especially a 3rd party application.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I second Jeff's last tag because some vendors demand to be the owner of a database. DB_OWNER does not cut it because they don't check roles, just who owns the database.

  • Agree with Jeff - it's not worth to change db owner to SA automatically.

    It may affect ex-owner to lose access to the database.

    Ex-owner should be created in the database as an user with db_owner role.

    But it is not 100% warranty also that application will work fine after that.

    I'm not sure if it's applicable to the modern versions of SSMS, but old ones do not show db properties is db owner is missing (login is deleted).

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

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