Forum Discussion
Helper IIISSAS Tabular model Row Level Security is not working in Power BI Desktop
Hello -
We have set up a SSAS Tabular Model and created 2 roles as below.
1. Admin - Administrator, Full Control
2. Employees - Read Only
There are 2 users added as members in Employees role and we applied Row Level Security using filter on one of the tables. We deployed it successfully on server and tested, it shows correct results in Browse queries for each role.
Then we opened Power BI Desktop on one of the employees machine, connected to Analysis Server database using live connection. Employee got access to model and all the tables, but all data too. Row-level-security didn't apply at Power BI Desktop level when connected to Tabular model using Live Connection.
Am I missing here anything? Is there anything we have to do for username vs emailid? Because we added usernames to AD groups for giving permissions in RLS and Power BI refers email ids.
As part of this project we need to roll out the Model to the users and not the reports. Users from different department are going to build their own reports and hence it is important to get RLS working at Power BI Desktop level.
Thank you,
It should work on both desktop and service this case.
Last test I can think about is, create the 2 measures I mentionned above, display it in desktop by these 2 users to see what they actually see.
check ou this blog to get better understanding about this test : https://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/
19 Replies
leedAdvocate II
mittalpatel130Hello Leed -
Thank you for your response.
Can you please brief me on how to test with USERPRINCIPAL()?
Thanks!
- leed
The same way you did with USERNAME(), you just needed to replace the last one by USERPRINCIPALNAME().
On Power BI Service, unfortunately, if you pass by Live connection, you need to have a real user for the test, you cannot use RLS.
Best,
DA
- v-eachen-msft
Community Support
Hi mittalpatel130 ,
You could refer to this document :
https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular
It details how to set up RLS in SSAS Tabular model.
- mittalpatel130
Hello..
Yes we implemented Tabular model as suggested in this article. RLS works as expected when you publish and share reports with end users.
But it doesn't work when end user connects to Power BI Desktop and Tabular model on local machine. We don't see RLS applied for that user in Power BI Desktop.
Hope this clears my question.
Thanks!
- leed
You need to test it in Power BI Service. It will render the correct USERNAME.
You can create 2 measures, 1 = USERNAME() the other one = USERPRINCIPALNAME()
Then publish to the service, the result should be different.
I am have a similar problem and I can't get rls to work in ssas, excel or power bi. Below is the model:
Also, I have tried ='DimServer'[ServerKey]= LOOKUPVALUE( 'DimServerSecurity'[ServerKey], 'DimServerSecurity'[UserName], USERNAME() ) and ='DimUser'[UserName]=USERNAME()
