Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  SQL Server Administration (2008)  Role Based Hierarchy

Author  Topic  pkettley Starting Member 1 Post Posted - 2011-03-23 : 10:31:22 Hi All, Based on 2005/2008 SQL Server. In my testing, I want to create role based hierarchies for multiple schemas, and find for some reason members get added to parent roles when they are part of child roles. Here's what I am doing (schema names have been changed for simplicity)... I have a schema which would contain a collection of tables that relate to a application let's call this payroll for now. I have a second schema which would contain a collection of tables that relate to another application let's call this HR for now. If I wanted to control write access to both of these schemas, I would like to create a "write" (owned by dbo) database role where I would add the users, and then add the role to specific database roles that is for the schemas mentioned above. Payroll Schema tables would have insert granted to database role named "payroll_write" and so on for HR ("hr_write"). You would then make database role "write" member of "payroll_write" and "hr_write", so they obtain the insert permissions of the group. Now why does a member of the role "write" get added to the "payroll_write" and "hr_write" when it's part of the "write" role, this appears in the list of members within the role, I can see the database role "write" and the members of that role in the list of members under "payroll_write" and "hr_write", so when you remove "write" from "payroll_write" the members are left in the "payroll_write". I would preferably like to implement something like active directory does. Kind Regards, Paul