June 19, 2007 at 1:23 pm
Greetings,
We are using SQL Server 2000. I would like to group users together by department and then put these department roles into functional roles such as in this scenario:
Role: Dept1
Role: Dept2
Role: FullAccess
Role: PartialAccess
I would like to give people in Dept1 full access by putting the Dept1 role in the FullAccess role and people in Dept2 partial access by putting the Dept2 role in the PartialAccess role.
The problem is that the permissions do not work out right. My understanding is that the union of permissions is used when determining what a user has permissions to do but that is not what is happening. For example, if I grant SELECT access to table A in the Dept1 role the users have access. If I then put the Dept1 role in the FullAccess role which also grants SELECT access to table A the users don't have access.
Can anyone explain this behavior?
Thanks
Tony
June 20, 2007 at 1:22 am
Few question from me.
1. Why do you want to add a role inside a role. You can directly give permissions to the role itself instead of giving to other role and adding this role to the other role.
2. When you give a permission to a role/user and again to a other role which has associated to it the permission should take effective. yo say the other way.
Can you let was know what is your exact scenario.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 29, 2007 at 8:34 pm
Sorry for the delay in responding Sugesh.
To answer your questions:
I want to create multiple roles because (for example) I want to use one role as simply a list definition (without specifying any permissions) (i.e. defining various departments) and then other roles which do define permissions. I then can add groups of users to various permission roles as a department.
SQL Server works exactly as I thought: Permissions are the union of all posibilities: hence if a role doesn't grant/deny permissions explicitly it has no effect and permissions are determined by other toles (such as 'public').
The fix was really stupid. Actually, somehow one of my role definitions apparently became corrupt. I simply deleted the role, created another role with the same name, set the permissions for that role, and then added the other role (defining my department list) to it as a member. Worked fine!
Thanks for the interest!
Tony
July 3, 2007 at 12:57 am
Ok no probs Andrew. good to hear that the solution was working for you.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply