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. 
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. 
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. 
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. 
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. 
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. 
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. 
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. 
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. 
2. Now scroll through the list until you find Order Count.
3. And as shown below put a tick next to Order Count.
a. 
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]](http://gqbi.files.wordpress.com/2014/03/clip_image0081.jpg "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. 
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. 
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]](http://gqbi.files.wordpress.com/2014/03/clip_image0141.jpg "clip_image014[1]"){kind=small}
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. 
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. 
2. Now scroll through the list until you find Amount
3. And as shown below put a tick next to Amount.
a. 
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. 
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. 
d. Then click Ok.
4. Now when we browse the cube under Measures we only see the following:
a. 
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. 