Very simple question - I have two database roles (Basic, Admin). Say I explicitly deny the Basic role from deleting from table A and grant that to the Admin role. If I am a user who is in both roles, can I delete from table A?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Never a simple answer...
For a direct DELETE, a user in both roles won't be able to DELETE
For indirect via a stored procedure, the permissions may not be checked if both table and proc have the same owner. So both GRANT and DENY will be ignored. This is called "ownership chaining"
Personally, I don't really use DENY. Here's why:
In your case, you only need to
If you're the dbo (or aliased to dbo) or sa (which is implicit dbo on all databases), then you automatically have all permissions. Otherwise, a
I'm not 100% certain on this, but I think if you have a user in two roles, it will apply all the granted permissions from the roles, then layer any