How to get Power BI Row Level Security (RLS) Role users, details and export to CSV
In a previous tweet on twitter, I had elaborated on how I had extracted the RLS Roles with the details and then exported it into a CSV file which then allowed the organization to keep an audit of the RLS for the dataset.
In the steps below I will show you how I did this.
In my previous blog post I explained how to export data from a Power BI report to a CSV file here: Exporting a Power BI Visual data to a CSV File in SharePoint
Creating the query to get the RLS Roles
Below is the query I created to get the RLS roles and details.
Some things to note:
- I am running a query using the XMLA end point, so this is only available via Power BI Premium or Power BI Premium Per User
- The person running the query needs to have admin permissions to be able to view all the data on the dataset.
- In my example I have a dataset in my Public Shares App Workspace which has got RLS roles created.
I used the Power BI Template file which you can find here: RLS Permissions.pbit
I then put in the PPU Workspace Name and PPU Dataset Name (This can also be a Premium Workspace Name)
I then clicked on Load to load the data.
Next, I was prompted to enter my login credentials. I clicked on Microsoft Account and then clicked on Sign In, once I had signed I then clicked on Connect.
Once the data was loaded this is what I could see below.
I then uploaded the PBIX to an App Workspace.
The final step was to follow the steps from my previous blog post on how to query the table and extract it to a CSV
This allowed me to then have the audit RLS data stored in SharePoint.
In this blog post I details how to get the RLS Roles and their details. And then keep a copy of the RLS roles in a CSV file in SharePoint.
As always comments and suggestions are most welcome. Thanks for reading