May 14, 2008 at 8:04 am
Just wondering if someone can help me understand how to nest database roles in sql server 2005. If I understand it correctly, the steps should be:
1. Create role 1
2. Add users to role 1
3. Grant permissions to role 1
4. Create role 2
5. Add users to role 2
5. Add role 1 to role 2
6. Grant "extra" permissions to role 2
After completing these steps, does role 2 have all of the permissions of role 1?
I tried to nest roles with the following code but it didn't work:
-- Create Logins
create login sqlUser with password='pa$$word', default_database = Test
create login sqlManager with password='pa$$word', default_database = Test
-- Create Users
use Test
create user sqlUser for login sqlUser
create user sqlManager for login sqlManager
-- Create Roles
exec sp_addrole HRUser
exec sp_addrole HRManager
-- Try to nest roles
exec sp_addrolemember HRUser, sqlUser
grant select on Employees to HRUser
exec sp_addrolemember HRManager, sqlManager
grant update on Employees to HRManager -- ("extra" permission)
exec sp_addrolemember HRManager, HRUser
After running the code I thought that the HRManager role would automatically be given SELECT permission on the Employees table since the HRUser role is nested inside it, but when I log on as sqlManager and try to select from the Employees table, the Permission Denied message comes up.
What is the correct procedure/code to nest roles in SQL Server?
Thanks!
May 14, 2008 at 9:27 am
You've got it a bit backward. A member role inherits the permissions of the role it's a member of. Using your example, HRUser role can select and update Employees - select because you granted it directly and update because it inherits it from HRManager. HRManager can only update Employees because that's the only permission you granted it.
You should have made HRManager a member of HRUser so it could select and update.
Greg
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply