Hi, I am trying to implement 'Row Level Security' in Power BI On premise-solution
1. Creating roles in 'Microsoft Power BI Desktop for Report Server - October 2017' works fine in desktop but then there is no option to add users to these specific 'Row Level Security groups' in Power BI report server. In Power BI online, there is option to add users/ groups once reports are published from desktop to cloud. I cannot see similar option in report server
2. I tried to create a SQL table and tried to implement row level security dynamically by reading data. The table is in below format
Then I created a Role which filters based on username() in 'Microsoft Power BI Desktop for Report Server - October 2017' Again, this is working fine in 'Microsoft Power BI Desktop for Report Server - October 2017' but when I "SAVE AS" the report to "Power BI Report Server" then the security is not working All users are able to see data for all countries. I am providing 'Browser' role to the users in report server. Any idea what I am doing wrong in the set-up? Or how can I implement row level security in reports saved to Report Server?
As far as I know still, it was not implimented, but we can use another way to create a measure as given below
Data[UserName] = USERNAME())
above DAX, Data is the table name and Data[UserName] is the filed. User name format is "<domain\username>". after creating the measure you can filter using report level as FilterRLS =1.
it will filter according the current user.. enjoy..
Please vote on the idea here:
I setup the measure like you mentioned, but am having troubles applying it as a report level filter.
I am able to set report level filters with table columns, but measures seem to not be allowed. I drag the measure over to the report level filter section but it will not take the measure. Would you please explain a little more on how to do this?
Thank you in advance 🙂 - This has been a real PITA, and I wish Microsoft would just add RLS to Report Server (On-Prem).
We have succesfully implemented Row Level Security, however we have had to create a SSAS Tabular Model to do it. Hopefully there are plans in the future to make Row Level Security easier.
The article below uses a good example as to how it can be done (same way as we implemented it):
Hope this helps.
Thanks for the info jdenne. It's too bad they haven't added Row Level Security into Power BI Report Server yet. I guess we will go down this route and see if it will work for what we are trying to do without creating too much additional work.
We're using a different method that implements RLS at the database level for Power BI reports. This is in SQL Server 2016.
You can't pass the logged-in user ID to SQL Server. However, if you use 'Windows Authentication' / 'As the user viewing the report' on the Data Source Settings for the report, you can use the SYSTEM_USER function to get the user ID within SQL.
We created a permissions table and a number of security predicates in our DB, using SYSTEM_USER to determine the logged in user. I like this method better than building filters in the report itself because you only have to build the security predicates once; every report you build using those secured tables automatically gets RLS, instead of having to incorporate it in every report.
I think security predicates are only available in SQL Server 2016 and later. In earlier versions you could set up stored procedures or views as the data source for your reports and build the RLS login in those directly.
A potential drawback with this approach is every report user needs a SQL login. We got around that by creating an Active Directory group, giving the group the SQL login, and putting all the report users in the group. That worked well for us because we were already using the AD group for other purposes and we had processes/procedures set up to maintain the group membership.
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!
Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications