This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Security in SSAS Tabular - creating and managing roles (this blog) Creating a permissions table Using CustomData to make row filters dynamic Controlling what a user sees using their logon name Assigning members to roles This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

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.

Security in SSAS Tabular - creating and managing roles (this blog)
Creating a permissions table
Using CustomData to make row filters dynamic
Controlling what a user sees using their logon name
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

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:

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:

Select this menu option while editing your model.

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

The 3 roles we're trying to create.

Start by clicking on the New button:

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:

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:

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):

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:

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:

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:

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:

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:

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:

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:

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:

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.

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR