Dimension Security in SSAS 2005 Cubes

Question

Hi, I am trying to implement security in SSAS 2005 cube. I have had my share of success so far, am able to create a role and apply the ALLOWED and DENIED sets to members I wanted to apply security on. Now starts the problem -

If I browse within the Analysis Services and select the role the security works as intended. But when I login as the user who is a member of the role I created (basically doesn't have access to some members), the security doesn't work with the current user property.

I understand the reason though - I haven't put the checks in place for the same. I know there is a function USERNAME that I can use to check user's identity, but instead of comparing that to a table containing valid logins, I want to check against a Role I just created. Is it possible to do this? If yes how?

PS - I don't want to create another table storing all valid users and their respective role information and want this to be dynamically done.

Thanks, Vineet

Answer 1

Well I guess I figured this out myself. There are two ways of doing this -

1. Create a table with user name and associate role. Then when applying denied/allowed sets, use the MDX expressions to find the role associated with the user and then apply restrictions. E.g. IIF(IIF(User_Name = [DIM USER].[DIM USER].[Domain Name].CurrentMember,[DIM USER].[DIM USER].[Role].CurrentMember, "Admin") = "RestrictedRole", True, False)

True - Visible, False - not visible

This is just an example and may need to be worked on.

1. Create a datasource on top of your cube with an additional property called "Roles".

E.g. DataSource = ;Initial Catalog = ; Roles=RestrictedRole

Anything using this datasource would apply restrictions automatically. Obviously this requires you to keep two copies of datasource - one restricted and other not.

Cheers!!