Introduction

On-premises SQL Server Analysis Services (SSAS) is still used today. The Tabular mode of SSAS uses the same underlying technology as Power BI models. This allows us to use SSAS Tabular Projects in Visual Studio to create Import Mode semantic models that can be published to Power BI or Microsoft Fabric.

Occasionally, there is a need for a finer grain amount of security for a semantic model which restricts users from being able to access certain rows of data. This can be accomplished through Roles and Row Level Security. While working Tabular Model projects in Visual Studio and publishing them to Fabric, there are a few things that I have ran into with RLS that is good to share.

Microsoft Fabric uses Microsoft Entra ID (formerly Microsoft Active Directory) for user security. When a user logs into Power BI/Fabric, their credentials are handled by Entra for authentication. Tabular Projects in Visual Studio were designed with on-premises Analysis Services first. Cloud based systems were later added and frankly, are not very intuitive.

Below I will cover some of the quirkiness of these projects. These instructions are for Visual Studio 2022, but should work with Visual Studio 2019 as well. Additionally, if you are working with Analysis Services Tabular Projects, you will need to install the appropriate VS Extension. For VS 2022, it is called Microsoft Analysis Service Projects 2022 and for VS 2017 and 2019 it is called Microsoft Analysis Services Projects.

Microsoft Analysis Service Projects 2022

Microsoft Analysis Service Projects

These extensions allow you to create either a Tabular Model or Multidimensional Model. If you are wishing to use these models in Power BI or Microsoft Fabric, then you will need to create a Tabular Model as Multidimensional Models are not compatible with the Power BI Service/Microsoft Fabric.

This article is assuming you have already created a Tabular Model and are wanting to apply Row Level Security to the model for publishing to Power BI Service/Microsoft Fabric.

Row Level Security

Before I go into details of how to implement Row Level Security, let me take a moment to step back and explain a little of what it is and how it works.

Row Level Security in Analysis Services allows you to restrict a users ability to see rows of data in a semantic model. This is done by creating Roles in the semantic model and assigning users or active directory security groups to the role as well as applying some DAX to the role indicating what rows that role can see.

For example, if you have an active directory security group of sales people that you want to make sure can only see their own sales in the semantic model, you can create a Role in the semantic model and call it "Sales People". You can then assign the active directory security group to the role. Let's say hypothetically there are two tables in your semantic model. Table one is [Sales] which is the sales records that include a foreign key to the second table called [Salesperson]. The [Salesperson] table includes information about the sales person including their name and email address. Their email address is the important part here.

In the "Sales People" role, you can add DAX to the [Salesperson] table so that the sales person can only see records that are tied to their email. This can be done by using code similar to the code below. It will vary based upon the names of your tables.

='Salesperson'[email]=USERPRINCIPALNAME()

For more information on row level security, you can check out Microsoft's documentation at the following link.

Dynamic row-level security with Analysis services tabular model

Note that the way they implement the above code in their documentation has a limitation where there can only be one entry per user in the table containing email addresses. This is because they use LOOKUPVALUE() which must return a unique value.

In their example, the user can only be associated with one territory. If you wanted a user to be associated with multiple territories, you would need to use something more like the code I showed above.

The last thing to mention in this section is RLS affects all reports created from the semantic model. This applies to Power BI reports, Excel spreadsheets, etc.

Active Directory and Microsoft Entra ID

In the examples Microsoft provides in their documentation, it assumes you are using an on-premises Active Directory for your user authentication. This article is focused on user in Microsoft Power BI/Microsoft Fabric which use Microsoft Entra ID (commonly known as Entra).

If you have an existing on-premises Active Directory, don't worry. You can use an on-premises Active Directory and sync your users to Entra. You will need to work with your IT team if this is not already setup. In case cost is an issue, using the basic Entra at this point in time is free for a basic install and has been since Entra has existed.

This will need to be setup before you can use Power BI/Fabric or it can cause some head aches for your IT team in the future.

Row Level Security: Microsoft Entra Users

Specifying particular users in a role as opposed to Entra Security Groups works very similar to how it works with on-premises users when adding them to a Role membership. The main difference is on the Members tab in the Role Manager, there is a new button called "Add External...". You will need to click it instead of "Add...". This will allow you to add users by their email address instead of their domain user name like "domain\user".

Row Level Security: Microsoft Entra Security Groups

Entra Security Groups are a little more complicated if you are not sure how to set them up in the Role membership. You cannot simply use the the [email protected] for specifying an Entra Security Group. You will need two specific pieces of information.

1. Tenant ID
2. Group ID

Before we can setup the Role membership, you will need to log into Azure and search for "Microsoft Entra ID".

Once you click on Microsoft Entra ID, it will take you to another little portal showing user and group information in your tenant. This is where we will get our first piece of information we need. In the main section there should be a section for "Basic Information". In that section should be your "Tenant ID". Copy that somewhere so you can reference it in a bit.

Once you have the tenant ID, on the menu to the left, click on "Manage" to expand the menu.

Next click on "Groups".

It will take you to a screen where you can search for the group you are needing to add to RLS. Type in the name of the group in the search bar and then click on the group you are looking for once it shows up in the search results. If it does not show up in the search results, you may not have access to search for it. You will need to talk to your IT team to get this information.

Assuming you were able to search for the group, it will take you to another page with details on the group. In the "Basic Information" section, copy the value next to the "Object ID". This is our second bit of information we need which is the Group ID.

Going back into our Role membership in Visual Studio, click "Add External...".

Instead of typing in an email address you will type in the Group ID @ Tenant ID prefixed with the text "obj:". If your Group ID is "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" and your Tenant ID is "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb", then use the value:

obj:aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa@bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb

Future Considerations

Microsoft announced at the Microsoft Fabric 2025 conference that they are introducing new security mechanisms in Microsoft Fabric which will include both row level and column level security. While that will definitely affect semantic models in Direct Lake Mode, it is not yet known how it will affect semantic models in Import Mode such as the ones created by Tabular Projects in Visual Studio.

Conclusion

Row Level Security in Tabular Models is a great extra level of security to prevent certain users from seeing data they shouldn't see. Analysis Services Tabular Models can be used on-premises on a Tabular Mode SQL Server Analysis Services server, Azure Analysis Services, and in Power BI Service/Microsoft Fabric.

I don't have a comments section yet, so feel free to send me feedback on this blog.