easyROLES the fastest way to set up SSAS roles

Testing role functionality

Once you have set up your security roles, it is a good idea to test them. Microsoft SQL Server Management Studio provides great functionality for role testing and making sure that data is secured as it should be.

For this example we will use the same Analysis Services Tutorial cube that we used in the previous post on creating SSAS roles.

Start by browsing the cube. Find Analysis Services Tutorial database, expand Cubes folder, right-click on the cube and choose Browse from the menu.

Browse SSAS cube

Drag and drop Employee dimension into the analysis. Expand Measures->Reseller Sales and insert Reseller Sales Count measure. Your Management Studio window should now look similar to the one below, showing all departments, titles, employees and their sales count. As you are likely the admin of this database, you see all the available rows. Third row is the only row containing data for Pacific Sales Manager. This means that if our role is functioning as it should, we should only see this one row of data once we have applied the role functionality to analysis.

Sample analysis of SSAS cube in MS Management Studio

Now it is time to test our newly created role and see the result as they would be presented to the user having access limited to only a portion of the data. In the top left corner of above screenshot from SQL Server Management Studio, you see the Security Context icon (two human figures which I have marked with a red circle). Click that button to see the Security Context dialog window in which you can click Roles radio button and choose the role you want to test.

Security Context dialog to use role capabilities

After you select the role you are testing (“Role-Pacific-Sales-Mgr” in the above example) click OK, and from then on Management Studio will only return results that are filtered by the rules which we have put into the role specification. Our role for Pacific Sales Manager therefore should return only one row of data, even though we insert the whole Employee dimension when creating the analysis:

View of a SSAS cube in Management Studio analysis after security role is applied

Having only one row in the resulting set of data proves that we have set up our new role correctly and when Pacific sales manager will query this cube he/she will see only Emploee data which is supposed to be visible. Thus we can conclude that role is set up correctly.

Tweet about this on TwitterShare on LinkedInShare on Google+Share on FacebookShare on StumbleUponEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *

We want you to be comfortable with your purchase and therefore offer 30 day 100% money back guarantee.