question about database roles

  • Hi,

    I wanted to know what are the net effective permissions applied to a user if both the database roles "db_datareader" and "db_denydatareader" are selected for a user. I could understand from BOL that these two are complimentary roles to each other but wondering why sql server let us select both for a given user.

    Thanks,

    Phani

  • Phani,

    the effective permissions would be reading on all tables and views will be denied. There's one exception, that is if the user is also a member of the db_owner group or sysadmin. In that case the deny won't have any effect.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • It is incorrect, at least in SQL Server 2000. In SQL Server, DENY has higher priority over other roles. DENY overwrites other roles even for db_owner. But DENY would not overwrite sa.

    I tested an account with both db_datareader and db_denydatareader roles. Then, open QA using this account. This account does not have any permission to read any data from both tables and view.

    Further more, I grant this account with db_owner, and continue to test. I have other permission except datareader.

  • To answer the question of 'why' it appears this was a design flaw IMO...


    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • No the idea is to apply most restirctive. It is not a design flaw as you can have a user who is a member of 2 different Roles in the system and each role could have opposing permissions or even the user has deny but added to a group thathas grant. It is up to you to setup and maintain the permissions so you get the effect you want.

  • SQL Oracle is right and I was a not clear enough.

    You can deny reads or writes to memebrs of the db_owner role, but you can't do it for the actual database owner (dbo).

    Markus

    [font="Verdana"]Markus Bohse[/font]

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

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