Dynamic Security in SSAS Cube



For implementing dynamic security in your SSAS cube you need make little change in the Dimension model.

Steps:


1. Assuming you already have a dimension to store the employee credentials for whom you want to apply the security. If such dimension is not there then create one.In AdventureWorksDw we have such a table called DimEmployee

2. Decide what you want to secure, let’s say Customer dimension members

3. Create a fact table (factless) relating the Customer and Employee dimensions

clip_image002


4. Create / modify the cube to include the following

clip_image004

clip_image005

5. Add the test data (in employee table)

clip_image006

6. Test it

Connect to the cube through any client (Cube browser/SSMA/Excel) as a test user and you will see the dynamic security working.

If anybody need this sample write to me at azazr@microsoft.com

Comments (12)

  1. spkn says:

    Azaz Rasool:

    Nice post on SSAS dynamic security. Trying to implement based on your post and got struck with following error. Appreciate you help.

    MDX Query:


    EXISTS ( [Dim Account] . [Dim Account]. Members,

    STRTOSET ( " [Dim Employee] . [LoginID] . [" + Username + "]"),

    ‘Fact Secure Account’

    )


    Getting errors:

    Check MDX script syntax failed because of the following error:


    An error occurred in the MDX script for the dimension attribute permission’s allowed member set: The dimension ‘[Dim Employee]’ was not found in the cube when the string, [Dim Employee].[LoginID].[CORPName], was parsed.


    Appreciate your help.

  2. Jamin says:

    I’m having some trouble with the Username function.  If I use:

    EXISTS([Dim Account].[Dim Account].Members,STRTOSET("[Dim Employee].[LoginID].[domain/user]"),’Fact Secure Account’)

    it works fine, but if I use the Username function then it doesn’t.  I thought maybe it was the format that Username function returned so I check it via:

    WITH MEMBER Measures.x AS UserName

    SELECT Measures.x ON COLUMNS

    FROM [Item In]

    The domain/user string from my dimension and the Username function are identical.  Do you have any suggestions?

    Thanks,

    Jamin

  3. Sonal Agarwal says:

    Hi Azaz,

    I am encountering the following error after following your suggested approach:

    Check MDX script syntax failed because of the following error:


    An error occurred in the MDX script for the dimension attribute permission’s allowed member set: The dimension ‘[Dim Employee]’ was not found in the cube when the string, [Dim Employee].[LoginID].[CORPName], was parsed.

    Can you please help me with this?

  4. Sonal Agarwal says:

    Hello All,

    I got the resolution for my problem.

    I was trying to implement custom security on database dimension instead of cube dimension. So, when I tried the same thing on cube dimensions, it worked perfectly.

    Thanks for this great article.

  5. hi nice information for the dynamic security services if we want that. ave and safe our home or our property than security is very necessary for us.

    Thanks for sharing.

  6. James says:

    Hello,

    Could you please share me this sample ?

    Thanks!

  7. aj says:

    Thanks, Sonal. I've been battling the same problem for days. I was trying to install the script at the Database dimension level and not at the Cube level. Once I figured that out, the MDX script worked. Who knew that there were dimensions on the Database level. Thanks.

  8. Paul Schuliger says:

    Does the diimension security work when the dimension is used as a page filter?

    I have it working when the dimension is used in rows and columns but cannot get the security to apply when the dimension is used as a page filter.  This becomes an issue when a dimension has thousands of values and a user does not want to have to search through them each time – they just want to see what is applicable to them.

  9. Rajesh K Singh says:

    Nice post. I will try and build using my current project scenarios.

  10. Hennie de Nooijer says:

    Great. Implemented like this but now i've users who wants to see all customers? Can i build something with the All members?

  11. M says:

    The check syntax works but when I go to my browser and change to a non-admin user then I get the user either doesn't have permissions to the database or the database doesn't exist…any ideas?

Skip to main content