How to use security roles to restrict access to your SSAS Tabular model
Part four 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
  3. Using CustomData to make row filters dynamic
  4. Controlling what a user sees using their logon name (this blog)
  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.

Controlling what a user sees using their logon name

DAX exposes a function called USERNAME(), which - as the name would suggest - returns the name of the current Windows user.  You can use this to customise what a user sees.

Throughout this blog page, assume that my Windows user name is TREETOPS\Wally.Owl.

A simple test for a user in a row filter

Let's start with a really simple role, which would show frogs to me and nothing at all to anyone else:

Andy frog role

Client tools  using this role will only see data for frog products, viewed by people who are logged on as me.

Here's a typical pivot table viewed using this role:

Pivot table using role

Because I'm logged on as me, I get to see frog data; no one else would even see this.

Using a permissions table to control access by logon name

You could adapt the permissions table that I showed earlier in this blog to filter who sees what.  To do this you'll need a permissions table - you could download and run this script in Management Studio, although it might be quicker to create the table manually:

The user permissions

Don't forget you'll need to change the user name to your own logon name!

Now import this into your model:

Importing permissions table

Import the table in the usual way.

Create a role as follows:

Region role

The role will show data for a transaction, for example, if it can find a row in the region permission table which matches both the currently logged on user and the region for the transaction.


Here's the expression should you want to copy it:







You can now analyse the model using an Excel pivot table, assuming the role you've just created:

Region role Excel

Show a pivot table using this role.


The result makes it all worth it!

Only my data appears

The only data I can see is for the two regions assigned to me in the region permissions table.


This is almost perfect - but it would be nice if I could control access by groups, rather than users ...

This blog has 0 threads Add post