How to use security roles to restrict access to your SSAS Tabular model
Part two of a five-part series of blogs

Perspectives in Analysis Services let you show different parts of your tabular model to different people, but they don't have any security. To ensure that the right eyes see the right bits of your model, you'll need to create and manage security roles, as explained in this blog.

  1. Security in SSAS Tabular - creating and managing roles
  2. Creating a permissions table (this blog)
  3. Using CustomData to make row filters dynamic
  4. Controlling what a user sees using their logon name
  5. Assigning members to roles

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 26 February 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating a permissions table

The obvious disadvantage of roles so far is that they take ages to set up, and whenever you need to change them you have to make design changes to your model.  Time to improve this!

The concept of a permissions table

Much better if you could create a table in SQL Server like this:

Permissions table

This table allows you to specify which roles can see data for which regions.

For example, here's what you should see if you view sales by species and region using the Northern region role:

The Northern region role

Anyone in the Northern region role should only see data for 3 regions.


You can follow the rest of this page by downloading and running this file in SQL Server Management Studio (it will create a table called tblPermission which should look the same as the table shown above).

Loading the permissions table

First create the permissions table (perhaps using the script above) in Management Studio, then load it into your model:

Loading permissions table

Load the permissions table into your model in the usual way.

You should now hide this from client tools:

Hiding from client tools

You really don't want the contents of this table becoming part of the pivot table field list!


Note that this table shouldn't be linked to any other (so don't create any relationships for it).

The CONTAINS function

To get our role to work, we need to find out for any row of data whether the region to which it belongs exists as an item in the permissions table for the role currently in use.  We can use the CONTAINS function, the syntax for which is:

Name of a table,

Name of a column within this table,
Value for which we're looking.

Name of a second column within the table,
Second value for which we're looking,

... )

To get this to work, create a role (I'm using the example of the Northern region role) with permissions set as follows:

Northern manager role

The DAX expression returns True for any row of data if we can find a region matching the row's region name for the Northern region role.

For a transaction within the Trafford Centre (a big - at least by UK standards - shopping centre near Manchester), this formula would return True because of this row:

The Trafford Cente row

If viewing a pivot table using the Northern region role, the CONTAINS function would (for a Trafford Centre transaction) find a region matching the current one as highlighted here.


Should you wish to avoid having to type in the DAX expression, you could copy this:




"Northern region",




Hiding the permissions table

It's probably a good idea to stop any role from seeing any of the data in the permissions table:

Hiding permissions table

Don't rely on hiding the table from client view, but also state that this role shouldn't be able to see any of the permissions rows.


Testing your permissions-based role

You can now check that your roles work by using them to show pivot tables:

Analyse in pivot table

Choose to present your model's data in a pivot table, as usual.


Now choose a role to use:

3 roles to choose

I've created roles for each of the regional managers in the permissions table.


You should now see data just for the region you chose!

Northern region

What you might get if you chose the Northern region role.


The great thing about a permissions table like this is that you can now change your permissions in Management Studio, without editing your model.

This blog has 0 threads Add post