The MDS Hierarchy Member permissions have been given a complete revamp in SQL Server 2012. As explained here, the hierarchy member permissions are purely optional and allow you to limit the access to a specific set of members.
Even though the actual member permissions are optional, you may have a need to purely allow a user to browse and/or update the Derived Hierarchy. This is also something that’s been affected by the aforementioned change, so I want to explore here how we would grant users access to a Derived Hierarchy.
In 2008 R2, on the model tab, you got a Derived Hierarchies node, as shown in the image below:
If we contrast that to SQL Server 2012 (albeit with a different model), we don’t have a Derived Hierarchies node:
Therefore, to access the actual hierarchy, according to this article, you need to give the user Update permission on the model. That’s not the only way to do it, but certainly working only at the entity level, or the member level, is not good enough. To explore, lets look at an example. I have the sample Customer model installed and I want to give a test user access to the following CustomerType hierarchy, which is just made up of the CustomerType and Customer entities:
Going to the User and Group Permissions, just quick confirmation that assigning Update permission to the Customer and CustomerType entities is not good enough. As shown below, the user does not have access to any of the Derived Hierarchies that I wanted:
Upping the permissions level a bit by granting read only permission to the model does the trick – the test user can now see the Derived Hierarchy and can also add members. This is thanks to 1) a Read Only permission at the Model level and 2) Update permission on the two entities that make up the Derived Hierarchy:
However, the simplified security comes with a slight drawback, which is to get access to the Derived Hierarchy we’ve now given the user Read Only access to all entities in the model:
You may not want to grant a user access to all entities, but thankfully it can be avoided by granting an explicit Deny at the entity level. For example, as shown below I’ve set an explicit Deny for the AddressType entity:
This will ensure that the user now doesn’t see the AddressType entity on the Entities menu. Now that the user can see the hierarchy, what about restricting access to the members? That’s a bigger topic, so I don’t want to cover it here, but I will just provide a useful tip. When you’re testing your security, don’t forget that member permissions are not applied immediately! The following article explains how to immediately apply the member permissions, which is something that I’d recommend carrying out whilst testing.