How to use security roles to restrict access to your SSAS Tabular model
Part one 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 (this blog)
  2. Creating a permissions table
  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.

Security in SSAS Tabular - creating and managing roles

In Analysis Services you restrict access to data by creating roles, and then setting row filters controlling what each role can see.  Read on for how to do all of this!

Our case study

Suppose that you have the following product groups:

Product manager Can see
Furry Mammals
Scaly and slimy Amphibians and reptiles
Feathery Birds

You want to create roles so that (for example) members of the Scaly and slimy role can only see this data:

Amphibians and reptiles only

People in the Scaly and slimy role would see amphibian and reptile data only.


Creating roles (SSDT / Visual Studio)

This is the easiest way to create roles.  First go into the roles menu:

Roles menu

Select this menu option while editing your model.


Our eventual aim will be to create 3 roles as required:

Three roles aiming for

The 3 roles we're trying to create.

Start by clicking on the New button:

Creating a new role

Click on this button to create a new role.


Give this role a name, (optionally) a description and say what members of the role should be able to do:

Assigning permissions to role

We'll allow people in this role to view data, but not to process it (ie not to load it into the model).

Creating row filters for a role

The next stage is to say for each role what data it should be allowed to view.

By default, members of roles will be allowed to view any data in your model (in this respect SSAS Tabular works in the opposite way to SQL Server, where a user starts with no rights).

To do this, against each table to which you want to restrict access type in an expression:

Setting row height

You can use double-headed arrows to widen columns and (as shown here) to make rows higher.


Type an = sign to begin your expression (you're trying to create a formula which evaluates to either True or False):

Typing formula

After typing =, you can use the usual DAX punctuation characters to make filling in a formula easier.


Here's an expression which would return True for any transaction if and only if it was for an amphibian or a reptile:

Row filter formula

Note that use of the double-pipe || to denote "or".

That is, allow people in this role to view any row of data if it's for an amphibian or a reptile.

Testing your role works using Excel

To check if your role works, choose to create a pivot table based upon your model in Excel:

Choosing a role

Assuming you've set up 3 roles, choose to use the Scaly and slimy one.


You'll then only be able to view data for the two species in this role:

Two species only

No matter how you slice your data, it will be as if Mammal and Bird data didn't exist.


Editing a connection to switch role

The role that you use when connecting to a model is embedded in the connection string:

Connection string showing role

You can manually edit the connection created for a pivot table to use a different role, as shown above.

Creating and using roles in Management Studio

As well as creating roles in Visual Studio, you can also create them in SSMS:

Creating a new role in SSMS

Right-click on the Roles category within a model, and choose to create a new role.


Now give your role a name, description (perhaps) and permissions:

Naming role

Here the role will be able to read data only.

You can now set row filters for the role, using the Row Filters tab in the above dialog box:

Setting a row filter in SSMS

The problem is that you don't have the intellisense available in Visual Studio, so you have to type in the formula without help.

Here's what the final formula might look like:

The DAX formula

Fingers crossed I've got it right!


You probably don't want to keep going into Visual Studio to manage your roles, so the next thing I'll show is how to create a permissions table in SQL Server controlling who can see which rows.

This blog has 0 threads Add post