asked
What is the purpose of db_denydatawriter and db_denydatareader
Can someone explain to me what the difference is between granting a user the database role db_denydatawriter against just not granting them anything. i.e. If they have NOT been granted db_datawriter for a particular database then is it not secure against them writing to the database. I am obviously missing the point of the deny roles somewhere here. Also what happens if you grant both db_datawriter and db_denydatawriter to the same user?
@StuKay - make sure to mark an answer as correct (in so far as it answered your question) so that others can easily see what helped you out. You can mark more than one answer right by clicking the tick to the left of the answer
The security model takes the most-restrictive approach. So whilst I might not have granted you explicit permission to write to a table, if you are in a group (windows group or database role) that has that permission then you will be able to. If I have explicitly denied you permission, then regardless of what other permissions you may have, you will not have permission to write. If you grant both db_datawriter and db_denydatawriter, then the most restrictive 'wins' and you will be denied.
The deny roles are an extra safeguard that you can use to make sure that certain logins or groups will never have the type of access that is specifically denied under the role. With nesting of groups in Windows and multiple role assignments sometimes individuals inadvertently end up with excessive permissions. This is just another level that can be applied to lessen these accidental cracks in the permissions hierarchy.
