Testing row level security in Tabular (DAX)

In this post I will demonstrate how to add row level security in your tabular model. This can be a useful feature if you want to restrict access to different dimension members for different users.

First of all we need to create a new windows user on your PC / server. Lets call this user “TestUser2”

Then I will create a new table with user names and which customers they should be able of viewing.

use [AdventureWorksDW2012]

create table [UserAccess] 
	FullDomainUserName varchar(200),
	DomainUser varchar (100),
	CustomerKey int

insert into [UserAccess] values ('yourdomain\TestUser2', 'TestUser2', 11511)
insert into [UserAccess] values ('yourdomain\TestUser2', 'TestUser2', 11512)

Open the Tabular model project in Visual Studio Data Tools.

Lets add this table to the model and then create a relationship to Customer and column CustomerKey. Click “Hide from client tools” on the UserAccess table.

After you have done this your model will look like this

Screenshot useraccess

Add a new role (Model -> Roles) called TestRole. Give this read permissions.

Then add this DAX to the DAX filter on the Customer table

='Customer'[CustomerKey]=LOOKUPVALUE('UserAccess'[CustomerKey], 'UserAccess'[FullDomainUserName], USERNAME(), 'UserAccess'[CustomerKey], 'Customer'[CustomerKey]) 

Role 1

Add your new Windows users to members

Role 2

Deploy your model.

Open a Excel pivot. When Connection to the SSAS server impersonate the TestUser2 user. After doing that your pivot will look like this.

Role pivot

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s