Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Simple SQL Server Reporting - Click Here to Register
 

Introduction to Dimension Security in SQL Server Analysis Services SSAS 2005


By:   |   Read Comments (13)   |   Related Tips: > Analysis Services Security

Problem
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?

Solution
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:

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

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:

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:

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:

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

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:

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:

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:

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.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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: https://www.mssqltips.com/sqlservertip/1773/how-to-enable-user-defined-hierarchies-in-sql-server-analysis-services-ssas/


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

 

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

I did another tip that you may be able to use. Here's the link: https://www.mssqltips.com/sqlservertip/1844/sql-server-analysis-services-ssas-dimension-security-stored-procedures/

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

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

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 05, 2013 - 5:33:32 PM - Ray Barley Back To Top

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 05, 2013 - 4:48:05 PM - Raj Back To Top

Hi Ray,

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

 

Thank You,

Raj


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

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 04, 2013 - 6:18:06 PM - dreamyorke Back To Top

Awesome, ty so much ^^


Tuesday, June 04, 2013 - 3:05:09 PM - Raymond Barley Back To Top

Take a look at this link:

http://www.rdacorp.com/2009/01/advanced-dimension-data-security-with-sql-server-2008/

 


Tuesday, June 04, 2013 - 1:17:20 PM - dreamyorke Back To Top

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?

Thanks

dreamyorke


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

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: http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.attributepermission.visualtotals(v=sql.90).aspx

 


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

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.

thanks

Alaattin


Learn more about SQL Server tools