Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Never a simple answer...

For a direct DELETE, a user in both roles won't be able to DELETE
DENY always has precedence when permissions are checked

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 GRANT DELETE to the Admin role. The Basic role needs neither DENY nor GRANT (but run REVOKE DELETE to remove the Basic DELETE permission)

  • The lack of a DELETE permission (by REVOKE) means a DELETE will fail for a Basic only user. DENY isn't needed to prevent this.
  • An Admin user (whether in both roles or not) has a GRANT so the DELETE will succeed. No DENY to block them
share|improve this answer
    
That's what I wanted to know - thanks! –  Dan Appleyard Dec 6 '11 at 20:43

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 DENY anywhere on the permissions chain overrides any GRANT.

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 DENY over the top.

share|improve this answer
    
If I had to use DENY, I'd have a special role for this to separate GRANT and DENY because of the precedence issue –  gbn Dec 6 '11 at 20:29
    
I can't think of many use cases for DENY instead of REVOKE, personally. Using DENY is effectively saying "okay since you're in this role, you're NEVER going to have access to object X, no matter what other roles you have". –  Simon Righarts Dec 6 '11 at 20:32
    
yep, same here. –  gbn Dec 6 '11 at 20:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.