Dimension Data Security and Cube Script

If you have worked with Analysis Services dimension data security for a while, you know that MDX allowed/denied set expressions can become rather complex. You might be tempted to externalize these expressions in the cube script for brevity or reusability reasons. For example, you might decide to add a calculated member that returns a list of allowed partners that the user is authorized to see over a factless fact table as follows:

CREATE MEMBER CURRENTCUBE.[Measures].[PartnerSecurityFilter] AS “Exists([Partner].[Partner].[Partner].MEMBERS, StrToMember(“”[User].[Login].&[” + Username + “]””), ‘Bridge Partner Security’; 

Then, in the allowed set you might try reusing the member definition in the allowed set expression, such as:

StrToSet([Measures].[PartnerSecurityFilter])

This sounds like a great idea but it doesn’t work. Specifically, you will find that it works for the first member of the SSAS role that connects to the server but it doesn’t work for any other members of the same role who connect to the server. To make things worst, the other users will inherit the same security access as the first user and this opens also security vulnerability.

While I am not excluding the possibility for a bug, I think what’s going here is related to the fact that the MDX script is evaluated after dimension data security is applied. What’s weird is that even dynamic expressions, such as StrToMember or StrToSet don’t work. I wasn’t able to find a workaround besides having the entire logic in the allowed/denied set expression and not referencing the cube script at all. If dimension data security becomes complicated, consider externalizing it to an Analysis Services stored procedure.