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
Exporting a Power BI Visual data to a CSV File in SharePoint
This allowed me to then have the audit RLS data stored in SharePoint.
Summary
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
[…] Gilbert Quevauvilliers needs a report, stat!: […]
Good work mate, I have a question, is there a way where we can expand your solution to all workspaces rather one by one?
Hi Gilbert,
Thank you for the process, is there a way where I can modify the Power BI template in order to extract for all workspaces rather than only 1 at a time?
Hi Nikhil,
Unfortunately, that you can only do it once at a time.
Thank you for this article
where can i find the Workspace URL?
Hi Sven, it is in the URL after the groups/ section
Hello Gilbert!!, Thank you for this article, unfortunately I am not able to access the template file. Can you please help me with it.
Hi Chetana,
I clicked on the link and it is working for me.
Maybe if you could try from a different internet location to see if the link works?
Hi Gilbert,
Thank you for the write up. I have tried the same to extract the RLS information but unfortunately ended with below error. I’m accessing the XMLA template using Premium per user, but the workspace is not the premium one. As per your document it is still okay. Can you please explain how can I resolve below error?
DataSource.Error: AnalysisServices: The ‘XXXXXXXXX’ user does not have permission to call the Discover method.
Technical Details:
RootActivityId: 57621149-f305-45de-bc30-6808d2a7cb93
Date (UTC): 8/6/2024 2:23:52 AM
Details:
DataSourceKind=AnalysisServices
Hi Bharath,
Can you please make sure when connecting you also put in the Semantic Model name?
Hi Gilbert,
Thank you for the reply. Yes, I have inputted workspace url and semantic model name in variables as below,
PPU URL: powerbi://api.powerbi.com/v1.0/myorg/SAP%20Finance%20Reporting%20-%20Production
PPU Dataset name: HWP Report
For dataset name, do we need to input as above or something like in this “491b4db8-ce60-4441-b4ee-ab66dbf4606b” powerbi service?
FYI, I’m already having admin access in above workspace.
Thanks.
Hi Bharath,
Can you make sure that you have got write access to the Semantic Model permissions?
Hi Gilbert,
I’m concerned about this access. May I know how can we check whether we have write permission? Is it specific to model or in admin settings? If you don’t mind, please provide me the steps to see the permissions.
Thanks.
Hi Bharath, here is how you can add permissions https://learn.microsoft.com/en-us/power-bi/connect-data/service-datasets-manage-access-permissions
Hi Gilber, i have the same issue. On a Premium PPU Workspace i can use your report to dsicover roles. But on a standard workspace i get the same error about not having the rights to use the discover method. I also got Admin access in the workspace, so i also got the write access to the semantic model.
Hi Sven,
This is because if the workspace is not in a PPU or Premium Workspace you cannot query the XMLA endpoints to get the required data.
Hi Gilbert,
Sorry for repeated questions.
As checked, the access related report, dataset, workspace are having full access. Only thing to note here is Workspace is not in Premium but the user I’m using for authentication is Premium per user. Is there any way to achieve this?
Thanks.