Forum Discussion
Row Level Security in Power BI Report Server
I'm trying to do something similar with the SQL server security table but ran into problems.
I have my table of users and can filter the SQL queries fine using the current user. However, when I run a scheduled refresh of the data in Report server it uses the specified credentials and so returns no data.
I tried creating a measure using CurrentUser = USERNAME() and then filtering the model by this but it won't let me use this measure in a table filter.
I want to refresh the data with the admin credentials and return all rows, then filter by the current user.
Any suggestions?
If I understand what you're trying to do, you're NOT using a security policy in SQL Server for RLS. I say that because that technique doesn't work if you're doing scheduled refreshes, it only works for direct query. If I've misunderstood, my apologies.
So with the assumption I understand what you're trying to do, here's an approach:
- You'll need a table in your report that links user ID's to whatever the filter field is. Let's call it SalesTerritoryRLS. It only needs to have two columns, one with the user ID and the second with the filter value. It's the table I gave in step 1 of my example earlier in this thread.
- Create a measure that looks like this: filterRLS = CALCULATE(COUNTROWS(SalesTerritoryRLS), SalesTerritoryRLS[NetID] = USERNAME())
- Now add a visual level filter to the visual that's displaying the data you want to filter where filterRLS is 1
I've done this as proof of concept, but not tried it in anything that's been deployed to a large set of users, so it's possible there are problems with this approach I've not run across. In my proof of concept it worked fine.
Hey,
I'm not sure if you are aware of this recent post by Christopher Finlan that you can find here:
https://powerbi.microsoft.com/en-us/blog/power-bi-report-server-release-timing-update/
Here it is stated that with next release of Power BI Report Server that is scheduled for January 2019, it will then support Row Level Security :-)
Regards,
Tom
Thanks davidm5.
So if I have a person with 2 territories in the Territory table how will this filter display both territories?
So for example, if I have a pie chart and want to filter it to only show the regions the user has in the SalesTerritoryRLS table, I would set the filter to filterRLS >= 1?
Thanks.
No, you should still use filterRLS = 1. You just need a row per sales territory/user in the filter table.
The measure is evaluated per row of data being displayed in your visual, so as long as there's a single row in the filter table with that sales territory/user combination the measure should still evaluate to 1. This is working for me in my proof of concept.
Great that works perfectly. I was just unsure about how it was evaulating it.
Can I use that measure in a slicer? So it filters my slicer to only show the matching Territories?
I think what you want is to have a slicer that shows only the sales territories that the logged-in user is allowed to see, that you want to do that by showing the sales territory in the slicer and filtering the slicer using the filterRLS measure. Problem is that you can't put a visual filter on a slicer.
There may be some kind of hack or workaround, but not one I'm aware of off hand. Best I can think of is instead of a slicer, having a table that lists all the sales territories and when the user clicks on a row in that table it filters everything else on the page.
Hi,
I applied the visual filter and it works but when the report is published in the report server, the end user has the filter pane that can use to cancel the filter applied for each visual.
Your idea is great but is there any way to avoid the end user to edit the filter from the front end report.
thank you for your help
Unfortunately not that I know of. That is a huge problem with this approach. It's more a Row Level Suggestion than actual Row Level Security - a convenient way to pre-filter a report for users, but not true security.
Hi,
Thank you for your reply, I was looking if there is a way to use a measure as report level filter or as slicer.
A slicer can be used in hidden page ans sync to be applied to all the report.
But I'm unable to find a way to use a measure as slicer.
