Forum Discussion
Row Level Security in Power BI Report Server
Helper III'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?
Helper IIf 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.