Analysis Service supports role based model to secure cube data. Data can be secured at object level, cell level (Cell Data Access) and at member level (Dimension Data Access). There are relative merits of each approach, which isn’t the topic of this post but more details can be found here. A user can be a member of more than one role and it is important to understand the behaviour of Analysis Services role permissions in such a scenario. Analysis Services role permissions are additive in nature. This means if you are a member of a role that has been granted access to an Analysis Service object; you have access to that object even if you belong to another role which denies access to the same object. In other word the least restrictive roles permissions override the most restrictive role permissions. This could throw some nasty surprises that you never expected.
To illustrate this, I’m using Dimension Data Access method on AdventureWorks cube.
A role called Australian SalesRep is created for the Sales Office in Australia and this role has been granted access only to the Australia member of the Sales Territory Dimension as shown in Screen Capture 1.
Another role called Deny CustomerInfo is created to deny access to customer data for privacy reasons as shown in Screen Capture 2.
Let’s say certain users in Australian Sales Office are required to have access to their sales data but no access to their customer data, so these users are granted access to both Australian SalesRep and Deny CustomerInfo roles. You would expect these users to have access to Australia’s Sales data but no drill-down to customer data. Now, let’s see what happens.