Dynamic Security in SSAS Cube

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

4. Create / modify the cube to include the following

5. Add the test data (in employee table)

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?