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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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