562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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!
Suppose that you have the following product groups:
|Product manager||Can see|
|Scaly and slimy||Amphibians and reptiles|
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.
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).
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.
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.
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.
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|
25 Aytoun Street