none
db_datareader & db_denydatareader

Answers

  • The same user can be added to both the db_datareader and the db_denydatareader roles. Deny always overrides a grant, so the user would not be able to read.

    This is the intended behavior. For example a group of users might be added to the db_datareader fixed database role, such as all Engineers. And then a subgroup might be might be added to the db_denydatareader fixed database role, such as contract Engineers. The result would put a contract engineer in both groups, and the deny would be the effective result.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, March 14, 2013 3:45 PM

All replies

  • Please brief you query & issue.

    Not able to understand.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, March 14, 2013 12:49 PM
  • The same user can be added to both the db_datareader and the db_denydatareader roles. Deny always overrides a grant, so the user would not be able to read.

    This is the intended behavior. For example a group of users might be added to the db_datareader fixed database role, such as all Engineers. And then a subgroup might be might be added to the db_denydatareader fixed database role, such as contract Engineers. The result would put a contract engineer in both groups, and the deny would be the effective result.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, March 14, 2013 3:45 PM
  • Greetings rohit/RIck.

    I do not have any query issue. I am trying to understand these two database roles.

    Rick I agree with your first point. Also agree with your second point.

    Say engineergroup consists of [1,2,3,4,5,6,7,8,9,10]  say this as group1 in Active directory.

    out of these contractengineergroup [4,5,6] say this as group2 in Active directory.

    as per your example group1 can use [db_datareader] and to restrict group2 we can use  [db_denydatareader].

    completely fine.

    now I take a different user and selected both roles [db_datareader] and the [db_denydatareader]

    means I am giving access and taking from them. in my opinion user should not succussed in creating the such kind of login.

    As SQL server allowing to create one is there any purpose for that? 

    Thursday, March 14, 2013 10:45 PM
  • Would laziness count as a reason? SQL Server would need extra code to validate this contradictory membership, and one could question whether it is actually worth that extra effort.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, March 14, 2013 10:48 PM