question

Samanthar-3682 avatar image
0 Votes"
Samanthar-3682 asked AmeliaGu-msft commented

security question

Hi All,

Have a question regarding database roles in SQL Server.
While assigning the database role for a database user, we have roles like db_datareader & db_writer roles.
Similarly, we have db_denydatareader & db_denydatawriter roles.

My question is, what is the use of these 2 deny roles.
If I don't want to a user to have SELECT , INS, UPD, DEL permissions, then I can simply uncheck or don't give  db_datareader, db_writer roles to that user.
What is use of db_denydatareader, db_denydatawriter roles. Is there any specific use case scenarios for these 2 db roles?

Thanks,
Sam

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Samanthar-3682,

How are things going?
Do the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·
RobbieVarn-7255 avatar image
0 Votes"
RobbieVarn-7255 answered

These two roles are typically not used. The only reason to possibly use these roles could be for piece of mind where an extra layer of security (for a deny) so that the read or write permission would not be granted by accident through some other group.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I will have to agree with Robbie. I have severe difficulties to find any practical use cases for these roles. Overall, DENY is something you should use sparingly, because you often run into problems since DENY trumps GRANT.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.