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
Deny is a trump over all other security. So, you may have a complex set up where a given user, because they're in a group, has access to a table or tables. But, you don't want them to see it or write to it, regardless of that group affiliation. You can use the deny as a final trump to be sure they don't have it.
But yeah, let's say there's nothing complex, at all, in any way, just a single login, no groups, no affiliations, if you never grant them access, you won't need to deny them anything.
----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
The Scary DBA Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
November 5, 2021 at 3:38 pm
Deny is a trump over all other security. So, you may have a complex set up where a given user, because they're in a group, has access to a table or tables. But, you don't want them to see it or write to it, regardless of that group affiliation. You can use the deny as a final trump to be sure they don't have it.
But yeah, let's say there's nothing complex, at all, in any way, just a single login, no groups, no affiliations, if you never grant them access, you won't need to deny them anything.
Thank you Sir. Got it.
December 21, 2021 at 6:13 pm
One example would be when you want to allow select to a 'tableA' from the accountingGroup. Then you realise that there is a member of that group who you would rather not have access to that data yet (say they are a new/junior member of the team). You still want that junior member to be a part of the accounting group so she/he can Select from the other tables accountingGroup has access to (just not tableA). So the solution here is add a DENY on tableA to the junior member.
----------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply