Securing the Cube - Expert Cube Development with SSAS Multidimensional Models - page 272

Now, here's the difficult part: how do we write an MDX set expression so that
Analysis Services will grant access to only the members we've specified? Our first
idea might be to filter the parent/child hierarchy using an expression like this:
NonEmpty (
Employee.Employees.Members,
(
[Measures].[Employee Count],
[Users].[User].[SQLBOOK\Marco]
)
)
Unfortunately, this will not work. The reason is that not only the Employees we want
Marco to have access to will have a value for the Employee Count measure, their
ancestors will too, due to the aggregation performed by the parent/child hierarchy.
A screenshot will show this much better:
If we focus on Marco's column, we will see that the three highlighted cells contain
a value, and they represent the Employees we've explicitly granted access to. Their
ancestors have values too, as a result of aggregation, so our set definition will return
a set containing Ken J. Sanchez and Brian S. Welcker, members which Marco should
not have access to. Since, as we know, granting access to a member grants access to
all of the descendants of that member, and since Ken J. Sanchez is the top member in
the hierarchy, this means that Marco will have access to all of the Employees on the
hierarchy. In fact, it follows that with this set expression if a User is explicitly granted
access to any member on the hierarchy, then they will be implicitly granted access to
all members on the hierarchy.