Tell me more ×
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.
up vote 2 down vote favorite

Is there any way to set priority for a role if a user falls in two roles?

EXAMPLE : If the user is a member of the manager and staff roles, I want to get the features of the manager on conflicting issues.

Delete is denied in staff while granted in manager and user1 is a member of both, then user1 could delete the data.

share|improve this question
1  
Is delete for staff DENYed, or merely not GRANTed? – Damien_The_Unbeliever Oct 2 '12 at 13:19
@Damien_The_Unbeliever DENY ed.. – Sayyid Sadik Oct 2 '12 at 13:20
1  
Out of curiosity, why? – Damien_The_Unbeliever Oct 2 '12 at 13:22
@Damien_The_Unbeliever all the employees are staff, but manager is manager and of course he is staff – Sayyid Sadik Oct 3 '12 at 4:35
Usually, DENY should only be used when you're dealing with nested roles, the "outer" role has been GRANTed some permission, and you wish to prevent someone with the "inner" role from using that permission. It sounds like staff is the "outer"-most role - they shouldn't have any DENYs. – Damien_The_Unbeliever Oct 3 '12 at 6:18

migrated from stackoverflow.com Oct 3 '12 at 7:40

2 Answers

up vote 2 down vote

Unfortunately for you, it does not work that way. Users in multiple roles are subject to the complete set of permissions from all of their roles, and deny permissions always trump grant permissions. You have to keep that in mind as you design your user and role assignments.

In this case, you might find that you don't need the DENY rule at all. A user can't do anything without explicit grant permissions, so if you remove the deny rule from your staff role they likely still won't be able to delete anything: the rule is redundant for them.

share|improve this answer
thanks for your answer – Sayyid Sadik Oct 3 '12 at 4:33
up vote 1 down vote

When a user is a member of multiple roles, their allowed permissions are the union of the individual role's GRANTs and DENYs - with DENYs always taking precedence over GRANTs.

So, in your case, if it's merely that staff are not GRANTed permission to delete, and managers are GRANTed delete, then a member of both roles will be allowed to delete.

There are no ways to change this policy - its built into SQL Server.

share|improve this answer
thanks, new knowledge – Sayyid Sadik Oct 3 '12 at 4:34

Your Answer

 
discard

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