SSMS Quick Tip

If you ever need to test Analysis Services roles, you can easily do so either by browsing the cube or by means of a query.

When browsing a cube through SQL Server Management Studio (SSMS) or Bisuness Intelligence Developer Studio (BIDS), you can select one or more roles to be enforced on data, by clicking the little “user” icon in the top left corner of the cube browser:

You will then be able to select on or more of the roles available on the database via a dialog:

This way of browsing roles has the following pros and cons:
Pros: Visual confirmation of PC Hierarchies
Cons: A lot of clicking top verify permission integrity

The other way of testing roles is through SSMS is by creating an MDX query. The trick is to click the options button when the connection dialog is active. The last tab allows you to specify additional connection parameters. The Roles property is described in detail at technet. Basically it’s a comma seperated list of roles (case sensitive). The trick is showed in the screendump below:

Pros: Custom queries
Cons: No visual confirmation of integrity, can be hard to visually confirm permissions on a PC hierarchy as an example