Forum Discussion
RLS for SSAS on Prem not Working
Hi All,
I'm working with a Power Bi connected in direct Query with a SSAS on prem. The report is published in Power BI Service.
They asked me to test some basic RLS rules based on the email of some users to restrict the access to data (some users can only see the data of certain branches).
In the Tabular Model I created a table that contains a row for the branchID, and a row for the user (using the email).
I created the Role, added some colleagues to the Membership for testing purpose, and write a DAX formula =
xxxxx.[CompanyID] = LOOKUPVALUE(yyyy[CompanyID], yyyy[User], USERPRINCIPALNAME))
We deployed the model but the colleagues still see everything.
We also tried to use a static RLS directly on the target table giving punctual values, but the RLS is still not working.
It could be a problem of Active Directory? Not matching the 365 account with the local AD account? Also on Power BI Desktop they still se everything.
Thanks in Advance
Hi, niklanzo91
I created your tables and used them in SSMS, the data in RLStable is a test mailbox:
In Power BI Desktop, tap the Modeling tab, and then select Manage roles. Create a new role, such as BranchRole. Add the following DAX formula to your BranchTable:
[CompanyID] = LOOKUPVALUE(RLSTable[CompanyID], RLSTable[UserEmail], USERPRINCIPALNAME())Publish to the Power BI service to configure RLS in the Power BI service. In the Power BI service, open the published report and tap Settings. In the Security tab, select the role that you created, BranchRole. Add the users or groups you want to test.
The creator's perspective is to be able to see all the data.
I have another user in my workspace. Until I added it as an RLS role, he couldn't see the data for my reports.
When I add it to the RLS role, he will only see the data that corresponds to his email address
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Plz share some sample data across the tables for us to identify the cause?
Can you confirm whether the user details are in the same format as the USERPRINCIPALNAME.
As it is connect live to SSAS, you can directly test the RLS within SSAS
Hi NandanHedge,
thanks for your reply.
I can confirm that the user email is the same of the result of USERPRINCIPALNAME (I tested it with a colleague).
Where can I test them within SSAS? With MDX Queries?
I leave here an example of the structure. I don't filter directly the fact table, but a dimension table related with the fact table.
This is the dimension table (branch_table):
CompanyId Description 01 UK 02 US 03 DE and this is the RLS_table. One user can see more than one branch:
CompanyId User 01 [email protected] 02 [email protected] 03 [email protected] Hi, niklanzo91
I created your tables and used them in SSMS, the data in RLStable is a test mailbox:
In Power BI Desktop, tap the Modeling tab, and then select Manage roles. Create a new role, such as BranchRole. Add the following DAX formula to your BranchTable:
[CompanyID] = LOOKUPVALUE(RLSTable[CompanyID], RLSTable[UserEmail], USERPRINCIPALNAME())Publish to the Power BI service to configure RLS in the Power BI service. In the Power BI service, open the published report and tap Settings. In the Security tab, select the role that you created, BranchRole. Add the users or groups you want to test.
The creator's perspective is to be able to see all the data.
I have another user in my workspace. Until I added it as an RLS role, he couldn't see the data for my reports.
When I add it to the RLS role, he will only see the data that corresponds to his email address
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
