Introduction to Dimension Security in SQL Server Analysis Services SSAS 2005

By:   |   Comments (12)   |   Related: > Analysis Services Security


I have seen the tip Dynamically Control Data Filtering in SQL Server Reporting Services Reports which showed how to leverage a SQL Server database table to filter sales data based on the user running the report.  I have a similar requirement to implement dynamic filtering except my data is in a SQL Server Analysis Services (SSAS) cube and the users query the data with a variety of different tools.  How can I implement dynamic security in my case?


SSAS has a robust security model that provides for dynamic filtering via a role-based security model.  You create roles and assign Windows users, Windows groups, or both to be members of a role, then specify various security settings for each role. 

As an example I have created an SSAS cube based on the SQL Server database used in the tip mentioned above in the problem statement.  The cube structure is as follows:


Our task is to dynamically filter the list of offices based on the user accessing the cube.  If the user were querying the cube from Excel, when the user drags the Office dimension onto the rows or columns of a pivot table, we want the list of offices to include only those offices that the user is allowed to view.  We will setup role-based security in the cube to accomplish this as follows:

  • CEO role can view all offices

  • ManagerRegion1 role can view the Baltimore, Philadelphia and Wilmington offices

  • BaltimoreSales role can view only the Baltimore office

Setting Up Role-Based Security in the Cube

To begin, open SQL Server Management Studio (SSMS), navigate to the Object Explorer, and connect to your Analysis Services server.  Locate your cube in the Object Explorer as shown below:

object explorer

Right click the Roles node, then select New Role to launch the Create a Role dialog as shown below:

baltimoresales role

I have named this role BaltimoreSales; it will be used to grant access to the Baltimore office sales data to the appropriate people.  Click Membership in the Select a page list of the Create Role dialog to specify the members of this role:

baltimoresales members

The above dialog has an Add button in the lower right corner (not shown above); click that button to locate the Windows users and/or Windows groups to add to this role.  In the example above I have added a single user to the role.

Click Cubes in the Select a page list of the Create Role dialog to grant the BaltimoreSales role access to the cube:


Click Dimension Data in the Select a page list of the Create Role dialog to specify which members of the Office dimension this role is allowed to view:

baltimoresales offices

In the above dialog we are able to specify the offices that this role can view based on the following options:

  • Select all members and manually deselect the members to deny

  • Deselect all members and manually select the members to allow

I chose the second option because if new members are added they will initially be denied; with the first option any new members added will be allowed by default.  Choose the appropriate option based on your circumstances. 

This completes the setup for the BaltimoreSales role.  Repeat the steps above to setup other roles as required.

Testing Role-Based Security in the Cube

To test our role-based security we'll use the Business Intelligence Development Studio (BIDS) that comes with SQL Server.  BIDS has a cube browser built-in to the SSAS project type.  The cube browser provides an option to specify the role(s) you want to use for testing.  The user account you use will need to be a member of each role that you want to test.  While not the best choice from a security standpoint, the account used to develop and test is often a member of the local administrators group which will work.

The cube browser is shown below; I have highlighted the toolbar option that allows you to specify the credentials to use to browse the cube:

cube browser

Click on the highlighted toolbar option to specify the credentials to use as shown below:

choose role

The default is to use the current user's credentials.  You can specify a different user or one or more roles.  Select the BaltimoreSales role then browse the cube.  The cube browser shows the following based on our role selection:

browse cube

Note that the only office shown is Baltimore and there is an information message stating that you are browsing the cube using the BaltimoreSales role.  If I change the credentials to use the ManagerRegion1 role, the cube browser shows:

browse cube2

In this case we see data for three offices.  Finally if I change the credentials to use the CEO role I see sales data for all offices:

browse cube3

You may have noticed that although the list of offices shown was what we expected in each case, the Grand Total reflected the total sales from all offices; it wasn't restricted to just the offices that the role is allowed to view.  This is the default behavior.  You can change this by returning to the Dimension Data dialog where we selected the offices that the role can view.  Click on the Advanced tab and click the Enable Visual Total checkbox.

The above solution will work fine for a relatively small number of users where the authorization rules are pretty static.  Ideally you should create Windows groups, assign users to those groups, then add the Windows group(s) to the cube roles.  In a future tip I will explore the other available options for implementing dimension security which are using MDX expressions and creating stored procedures using .NET code.

Next Steps
  • Download the sample code and experiment with it.  The archive includes a backup of the SQL Server database used to build the cube, the SSAS project, and a backup of the cube itself.  You can use SSMS to edit the roles and/or add your own roles.
  • Dimension security in an SSAS cube is much more robust than what we get with a SQL Server relational database.  This is good to know because your users will come up will all sorts of authorization schemes.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, July 12, 2018 - 12:47:17 PM - Raymond Barley Back To Top (76626)

One way you could restrict other dimensions by company is to create many-to-many relationships.  You have to associate each dimension with one-to-many companies.  Since the company uses role-based security, the only dimensions a user can see is for the companies he can access.

I did a tip that shows how to define custom reporting hierarchies.  You could leverage that.

Here's the URL:

Thursday, July 12, 2018 - 11:48:59 AM - Mobeen Back To Top (76625)


Hi Ray,

I have used the same steps you have mentioned and scenario works well for the company. But I want all the dimensions to work like that even if we do not use the company dimension. Is there any way we can restrict the data of all the dimensions and measures by company?

Tuesday, April 17, 2018 - 3:05:31 PM - Raymond Barley Back To Top (75722)

I did another tip that you may be able to use. Here's the link:

The tip was based on a project that I was working on where the security model was in place in the relational database and it was just too difficult to implement in SSAS. The tip shows how to execute a stored procedure in the relational database via the role-based security in SSAS. In SSAS you can access the USERNAME; in the relational database you need a stored procedure that accepts the USERNAME as a parameter and then you have to use the USERNAME in a query that will return the filtered list.

I've only used this a couple of times. I would say it may work but this approach is a bit complicated.

Tuesday, April 17, 2018 - 11:25:57 AM - Godard Pierre Back To Top (75720)

Hi Ray, what you described here is exactly what our security model looks like except that it has two more layers. Acct exec, client + the hierarchy that you described. How can I modify the example to implement my model? Thanks in advance for your help.

Monday, June 16, 2014 - 2:33:20 PM - Darren Handler Back To Top (32266)

I have setup a basic "Offices" dimension connected to a "Sales" fact table.  I have certain people that should only be able to see certain offices and their data.  I created a "Users" dimemsion, and a "UsersToOffices" intermediate fact table to link between Users and Offices.

Here is the layout:

dimUserMaster <- xrefUserOfficeMaster -> dimOfficeMaster <- factSales

In my OfficeMaster table, in addition to the OfficeKey which links into the xref table, I also have the usual attributes like County, State, Region, OfficeManager, BranchName, etc.  The xref table also has a UserID that links into the UserMaster table.

When I setup my Dimension Data security on dimOfficeMaster, the security is only enforced on the attributes I create the security for.

If I set this up for dimOfficeMaster:

exists([DimOfficeMaster].[STate].MEMBERS, {strtomember(" [dimUserMaster ].[Username].&[" + Username() + "]")}, "xrefUserOfficeMaster ")

This works fine when any particular user goes to look at the Sales amounts by State.  They only get to see the states they are allowed to see.  However, if the user goes to look at sales by county (Or other attributes), without State, they get to see all the counties and their amounts, even from the other states.

It seems silly that I should have to apply the same security over and over for every other attribute - and at some point, I'll get the message about having too many security definitions.  

I think I am missing something simple - how can I get that security to apply across all the attributes in the dimension? 

Thursday, September 5, 2013 - 5:33:32 PM - Ray Barley Back To Top (26630)

Go to the Dimension Data page under Create Role (this is shown in the tip above), select the radio button "Deselect all members" and don't check any of the members.  The role has no access to any member in the dimension.

Thursday, September 5, 2013 - 4:48:05 PM - Raj Back To Top (26629)

Hi Ray,

If there is another role say 'Executive'. This role should not be allowed to view office attribute itself of  "". How could this be possible. Please let me know your thoughts.


Thank You,


Wednesday, August 14, 2013 - 10:28:35 AM - ERIC LIU Back To Top (26306)

I think this still has a problem.

The case show here is a simple case. This solution, I think, still has problems.

For example, let's just look at pic about the BaltimoreSales role (the tenth pic). If we click the plus sign in front of dimension Office and look the members of 

Tuesday, June 4, 2013 - 6:18:06 PM - dreamyorke Back To Top (25292)

Awesome, ty so much ^^

Tuesday, June 4, 2013 - 1:17:20 PM - dreamyorke Back To Top (25284)

Hi Ray, 

is there a way to dynamically select the offices shown based on the current user? Ex: if I have a Dimension with the names of the possible users and the user "Bob" is currently viewing the data, is there a way to show "Bob" only the offices he is allowed to see without setting up a role for each and every other possible user?



Thursday, February 28, 2013 - 5:19:21 AM - Ray Barley Back To Top (22469)

I think what you want is the AttributePermission.VisualTotals property.  This is in the Analysis Management Objects (AMO) class library


Here's the MSDN link:


Wednesday, February 27, 2013 - 3:07:15 PM - Alaattin Back To Top (22451)

Hi Ray,

Those check boxes in Role called as "Enable Visual Totals" are unchecked by default. Is there a way to set them checked for all (here "all" means for all attributes in al dimensions in all cubes) ?

I checked some powershell scripts over cubes but could find a solution as this particular field do exist in another XML structure other than Roles' XML.



get free sql tips
agree to terms