April 26, 2005 at 3:52 pm
I'm hoping that someone can explain a problem I'm having grasping the concept of roles. I was experimenting in Northwind and created a generic user with public access to the database. I then created a new role and put this generic user in there. I went through the role granting, denying, and revoking privs on tables, views, etc. I tested my new permissions through query analyzer logging onto the instance as the generic user; seemed to work great. Then I started thinking ... does anyone know if you can have the same user/group in multiple roles in the same database and if the permissions have a heirarchy based on which role they are in? I guess an example would be if Bob was a company officer, but also a manager. He would be in a Management group, who could only select from certain tables, and in an Officer level group, who could run SPs and select from certain tables. Would one role's permissions override the other? Help please
Thanks,
Jennifer
April 27, 2005 at 2:24 am
Hi Jenifer,
The short answer is yes, one role would overrule another. SQL works on the least permissions principle, so you can have a login as a member of a custom role that allows him to select from every table but also a member of a role where select has been expressly denied for certain tables.
He would get all of the access associated with the first role minus the ones denied in the second.
Hope this helps
April 27, 2005 at 7:49 am
Thank you Mike for the reply; yes it helps.
Jennifer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply