How to setup basic dimension security in SSAS

Reposted from Jens Vestergaard's blog with the author's permission.

SQL Server Analysis Services offers the possibility to create roles, whereby you can manage the access certain users will have to data in the cube(s) on the database. The following is a basic tutorial on how to create a role Limited Access, used to limit the access to the Product dimension of the Adventure Works cube.

First we right-click the Roles collection on the database and select New Role:

Then we give the role a meaningfull name and perhaps a description to distinguish it even more:

Next we add the members of the role, eg. the users:

We can add as many users as we like, even groups to let he IT Administration handle the permissions:

We then need to decide which cube(s) the role is granted access to:

We jump a few steps here in order to get to the basic part of setting dimension security in SSAS 20008.
Select the Dimension Data tab, and select which dimension(s) the role is to be restricted on:

This particullar role is limited to seeing only members Accessories and Bikes, hence Clothing and Components are prohibited:

In order for us to test this newly created role, we can select to browse the cube using the credentials of the role.
This is done in the browse cube dialog:

We then select our new role:

And voilà, the permissions show up as expected:

For more advanced stuff on security in cubes, please refer to: http://www.ssas-info.com/analysis-services-articles/51-security

Jens has been working in the IT Industry since mid 90s. He started focusing in on the SQL Server Stack around 2003, mainly Analysis Services and the RDBMS. His blog is at http://www.t-sql.dk and he currently works for http://www.rehfeld.dk as a senior BI consultant.