Answered by:
db_datareader & db_denydatareader
-
While I was reviewing some SQL security concepts i come across the database roles.
(db_datareader, db_denydatareader). to give read only access issue "db_datareader".
I expected both cannot be issues and I was wrong. I was able to issue both. What is the story behind.
Google come up with nice material
http://sqlserverpedia.com/wiki/Database_Security_Tutorial#db_datareader.2Fdb_denydatareader
Unable to figure out exact difference. can any of you have some kind of example to show the difference.
Thanks in advance.
Question
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
- Proposed as answer by Sean Massey Thursday, March 14, 2013 8:39 PM
- Marked as answer by Maggie LuoModerator Sunday, March 24, 2013 2:28 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. -
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
- Proposed as answer by Sean Massey Thursday, March 14, 2013 8:39 PM
- Marked as answer by Maggie LuoModerator Sunday, March 24, 2013 2:28 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?
-