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

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

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:

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:

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:

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

Now import this into your model:

Import the table in the usual way.

Create a role as follows:

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:

=CONTAINS (

tblRegionPermission,
[UserName],

USERNAME(),

tblRegionPermission[Region],

Region[RegionName]

)

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

Show a pivot table using this role.

The result makes it all worth it!

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 ...

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

Controlling what a user sees using their logon name

A simple test for a user in a row filter

Using a permissions table to control access by logon name

Head office

London

Manchester