Partager via

Dynamic Security in SSAS Cube

  • Article

Technorati Tags: SSAS,Cube,Security,Dynamic Security,username,Analysis Services,SQL Analysis Services

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

  • Anonymous
    August 14, 2008
    PingBack from http://www.easycoded.com/dynamic-security-in-ssas-cube
  • Anonymous
    November 07, 2008
    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.
  • Anonymous
    September 15, 2009
    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 UserNameSELECT Measures.x ON COLUMNSFROM [Item In]The domain/user string from my dimension and the Username function are identical.  Do you have any suggestions?Thanks,Jamin
  • Anonymous
    October 14, 2009
    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?
  • Anonymous
    October 15, 2009
    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.
  • Anonymous
    May 14, 2010
    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.
  • Anonymous
    August 21, 2010
    Hello,Could you please share me this sample ?Thanks!
  • Anonymous
    March 09, 2011
    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.
  • Anonymous
    May 02, 2011
    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.
  • Anonymous
    May 19, 2011
    Nice post. I will try and build using my current project scenarios.
  • Anonymous
    September 15, 2011
    Great. Implemented like this but now i've users who wants to see all customers? Can i build something with the All members?
  • Anonymous
    November 01, 2011
    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?