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 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. Security in SSAS Tabular - creating and managing roles Creating a permissions table (this blog) 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 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.

Security in SSAS Tabular - creating and managing roles
Creating a permissions table (this blog)
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

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:

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:

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:

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

You should now hide this 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:

CONTAINS (
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:

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:

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:

CONTAINS (

tblPermission,

tblPermission[RoleName],

"Northern region",

tblPermission[RegionName],

Region[RegionName]

)

Hiding the permissions table

It's probably a good idea to stop any role from seeing any of the data in the 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:

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

Now choose a role to use:

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!

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.

Parts of this blog
Security in SSAS Tabular - creating and managing roles Creating a permissions table (this blog) 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