Database Roles dbo_owner and db_datareader

  • Steve Vassallo

    SSCommitted

    Points: 1635

    Hello all.. its been awhile and I have been way from SQL for a couple years.. Getting back into it now.  Quick question on permissions.  If someone is granted db_owner and db_datareader on a database, is their effective permissions db_owner?
    Just verifying that the permissions here are not setup as least restrictive

    Thank you
    Steve

  • Sue_H

    SSC Guru

    Points: 89891

    Steve Vassallo - Monday, June 5, 2017 12:05 PM

    Hello all.. its been awhile and I have been way from SQL for a couple years.. Getting back into it now.  Quick question on permissions.  If someone is granted db_owner and db_datareader on a database, is their effective permissions db_owner?
    Just verifying that the permissions here are not setup as least restrictive

    Thank you
    Steve

    Essentially yes. db_owner can pretty much do anything in the database  - which would include select against all tables and views like db_datareader.

    Sue

  • Steve Vassallo

    SSCommitted

    Points: 1635

    But just to be sure, if you have multiple roles checked, db_owner still trumps them all ?

  • Sue_H

    SSC Guru

    Points: 89891

    Steve Vassallo - Monday, June 5, 2017 12:40 PM

    But just to be sure, if you have multiple roles checked, db_owner still trumps them all ?

    Yes but it's the permissions that matter, not necessarily a role in particular. In general, permissions are cumulative with deny taking precedence.
    db_owner would be the role with the most privileges in terms of database roles. And as I said, they can pretty much do anything in a database.

    Sue

  • Erland Sommarskog

    SSC-Insane

    Points: 23754

    Sue's answer is correct as long as you start do dabble with DENY or roles like db_denydatareader. In difference to sysadmin, you can deny db_owner rights. And DENY always takes precedence over GRANT.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

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

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