SSAS (SQL Server Analysis Services) – Securing Measures in Measure Group for specific Roles

What I had to do today for the first time is to only show specific measures in the measure group for particular Roles (Users or AD Groups)

·         The reason that this was required is because we had sensitive information that was not for everyone’s eyes.

 

NOTE: From my recent review and read through from the following book, http://www.packtpub.com/expert-cube-development-with-ssas-multidimensional-models/book that the least restrictive will apply for a particular User.

·         So if User (BOB) belonged to one SSAS Role which only allowed access to the count of rows. And User (BOB) also belonged to an SSAS Role which has access to the financials he would be granted access to both the count and the financials.

·         Another thing to remember is that your calculated measures will be shown regardless of the Role permissions.

 

You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012

 

Example:

·         We are going to create a Sales Role, which will only be able to see the Sales Orders Measures group and associated Measures for the Adventure Works Cube.

·         We will also create a Financial Reporting Role, which will be able to only see the Financial Reporting Measures group and associated Measures for the Adventure Works Cube.

 

Creating the Roles

1.       The first thing that we are going to do is to create the 2 roles.

2.       Right click on Roles and New Role

a.        clip_image002

3.       It will default to Role.Role.

a.        Right click, select Rename and change it to Sales Orders.Role

b.       It will prompt you if you want to change the Object Name also, click Yes.

c.        clip_image004

4.       Next we will create our Financial Reporting Role.

a.        You can follow steps 2 and 3 above so that you will then see your Financial Reporting.Role.

b.       clip_image006

 

Adding Permissions and configuration to see particular measures

In this next section we are going to configure each of our Roles, and then configure which measures they can see.

 

1.       We will start with the Sales Orders.Role.

a.        In the General Tab, make sure you set the database permission for this role to:

                                                               i.      Read definition

                                                              ii.      clip_image008

b.       Click on the Membership tab and put in your domain users, or ideally your Domain Groups who are part of the Sales team.

c.        Then click on Cubes.

                                                               i.      Then next to the Cube Name of Adventure Works, make sure that you change the access from None to Read as shown below:

                                                              ii.      clip_image010

                                                            iii.      After allowing Read access click on the Save button, it will then prompt you saying that the following objects will also be saved:

1.       clip_image012

2.       Click Ok.

                                                            iv.      NOTE: You have to first save the access to the cube, if you do not do this in the next steps under Dimension Data you will NOT see the Measure Groups in order to change the permissions.

d.       Next click on Dimension Data.

                                                               i.      Where it says Dimension click on the drop and select the following as shown below:

1.       clip_image014

2.       Then click Ok.

3.       NOTE: You will see all of the Measure Group values.

a.        So you will need to know which measures are associated to your Measure Group.

b.       In our example for Sales Orders it was the following:

c.        clip_image016      

                                                              ii.      Now click on the Deselect all Members, this is so that we only need to select the Members that we WANT to enable.

1.       clip_image018

2.       Now scroll through the list until you find Order Count.

3.       And as shown below put a tick next to Order Count.

a.        clip_image020

                                                            iii.      Now save your Sales Order.Role.

2.       Now open your Financial Reporting.Role

a.        In the General Tab, make sure you set the database permission for this role to:

                                                               i.      Read definition

                                                              ii.      clip_image008[1]

b.       Click on the Membership tab and put in your domain users, or ideally your Domain Groups who are part of the Sales team.

c.        Then click on Cubes.

                                                               i.      Then next to the Cube Name of Adventure Works, make sure that you change the access from None to Read as shown below:

                                                              ii.      clip_image021

                                                            iii.      After allowing Read access click on the Save button, it will then prompt you saying that the following objects will also be saved:

1.       clip_image022

2.       Click Ok.

                                                            iv.      NOTE: You have to first save the access to the cube, if you do not do this in the next steps under Dimension Data you will NOT see the Measure Groups in order to change the permissions.

d.       Next click on Dimension Data.

                                                               i.      Where it says Dimension click on the drop and select the following as shown below:

1.       clip_image014[1]

2.       Then click Ok.

3.       NOTE: You will see all of the Measure Group values.

a.        So you will need to know which measures are associated to your Measure Group.

b.       In our example for Financial Reporting it was the following:

c.        clip_image024  

                                                              ii.      Now click on the Deselect all Members, this is so that we only need to select the Members that we WANT to enable.

1.       clip_image025

2.       Now scroll through the list until you find Amount

3.       And as shown below put a tick next to Amount.

a.        clip_image027

                                                            iii.      Now save your Financial Reporting.Role

 

Updating the cube with the Roles and testing.

The final step is to update the cube with the new roles and to test.

 

1.       What I did is I did a process Update on the Products dimension.

a.        NOTE: This just enables me to push the changes to the SSAS cube.

2.       Next I opened up the Adventure Works Cube and then clicked on the Browser Tab.

3.       Now where it says Change User click on the Icon.

a.        clip_image029

b.       This will then open the Security Context – Adventure Works window.

c.        Then click on Roles and click on the drop down and select Sales Orders

                                                               i.      clip_image031

d.       Then click Ok.

4.       Now when we browse the cube under Measures we only see the following:

a.        clip_image033

5.       And if you had to follow steps 3 above and change it to Financial Reporting you would see the following in the browser.

a.        clip_image035

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s