Imagine you have a requirement to capture the user accounts that log into your cube and based on their username they will only be permitted to see specific data.  This can be accomplished by creating dynamic or table based security in Analysis Services which is much less difficult to setup than it sounds like.  In this article I will walk you through the setup needed on the relational database side as well as Analysis Services side to get this working in your environment.

You will start by identifying what attributes you want to base your security off of.  For example, maybe I want User1 to only be able to see the sales for the states Florida, Georgia and Louisiana, while User2 can see California, Oregon and Washington.   Typically, if I were to create this security I would create a role for each grouping of states and add the appropriate users.  An easier way to maintain security like this example would be to store all this information in relational tables that way a DBA that may not be as familiar with Analysis Services can easily handle adding new users or modifying existing ones.  This table structure would look something like this:

FactSales is storing all the company sales, while DimState has all the possible distinct states that sales can occur in.  This is a very small scale example, but here’s in detail what DimUser and UserStateBridge tables store:

DimUser

This table simply has a key which is an identity column in this case and an account column which is the user account that you wish to grant access.  So example records would look like this:

UserStateBridge

This is a table that has a many-to-many relationship between the DimUser table and DimState.  This many-to-many relationship allows for one user to see many states and one state to accept many users.  Here’s a sample of what this table may look like:

I’m not going to show you what the State dimension would look like because this is just a typical dimension you see day to day.  The StateID here is the identity column from the state table.

Once this table structure is created your next step is to bring it into Analysis Services.  Here are the steps to follow inside BIDS to leverage these tables (I’ll assume you’ve already created a basic cube first):

· Add these new tables (DimUser and UserStateBridge) to you Data Source View.

· Create a new dimension based off the DimUser table.  Hide all the columns by changing AttributeHierarchyVisible to False.  You don’t want users to be able to view this dimension.  Process this dimension.

· Create a new Measure Group in the cube based off the UserStateBridge.  Delete all measure it creates except for the automatically generated Count measure.  Select the single measure left to be hidden by selecting it and changing the Visible property to False.  This will hide the entire Measure Group from your users but you can still use it when writing MDX.

· Ensure these two have a Regular relationship setup in the Dimension Usage table of the cube browser.  Often this relationship is created automatically.

· In the Solution Explorer, right-click on Roles and select New Role.  Give Read access in the General, Data Sources and Cubes tabs.  On the Membership tab add any groups or user accounts this Role will effect.

· On the Dimension Data tab select the State dimension from the dropdown list.  Then select the StateID(you would likely have this column hidden in the dimension) in the Attribute Hierarchy dropdown list.

· Inside the Dimension Data tab select the Advanced tab.  In the Allowed member set area enter this MDX:

The UserName() function will grab the user account that attempts to access the cube.  StrToMember converts a literal string to a dimension member.

· Last in the bottom left of your current screen check the box called Enable Visual Totals.

· Reprocess the entire SSAS database now.  When you navigate to the cube browser now you can test and make sure that your security is working properly by emulating one of the users you’ve added to the role.

After verifying you security in the browser if you still find problems with the results then your next step is to check the user and bridge table.  Again this is a very basic example of creating dynamic security so if you did something more complicated like based a role off of two bridge tables then you may run into conflicts between the two.  For example, say you not only want to base your security off of state but also on the store the items was sold at.  In that case you would create two bridge tables.  One for state, which we’ve already done, and a second for store built the same way the first one was.  So image your fact table had some results similar below:

When using multiple bridge tables in a single role you need to make sure that if User1 has rights to StateID 3 in the UserStateBridge table then he also needs rights to StoreID 4546 and 4578 in the store bridge table to be able to see each record when browsing.  This means the bridge tables can actually over rule each other if they do not have permission for both keys.  This is especially important if you’re using inferred members in you data warehouse using some kind of indicator like -1.  If there is a store in an unknown store and you assign unknowns to a -1 key value when you will want to make sure your users have access to that -1 store.

Adding new accounts is very simple now.  Add them to the DimUser table and then reprocess.  If you need additional states associated with users simply add them to the bridge table then reprocess the cube.

After reading this article if you still need more information on this topic I highly recommend the book Expert Cube Development with SSAS Multidimensional Models.  This book shows many different types of dynamic security including security based off of a stored procedure.