patternsqlMinor
Set priority for an SQL Server role
Viewed 0 times
prioritysqlroleforserverset
Problem
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#25334, answer score: 4
Revisions (0)
No revisions yet.