security question

Sam 1,371 Reputation points
2021-11-05T11:32:37.647+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,690 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Robbie Varn 346 Reputation points
    2021-11-05T16:02:24.283+00:00

    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.

    0 comments
  2. Erland Sommarskog 100.9K Reputation points MVP
    2021-11-05T22:50:41.563+00:00

    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.

    0 comments