How To get Users who are assigned to Power BI Row Level Security Roles (Report Server and Power BI online)

If you created a Power BI Report with Row Level Security (RLS), it is good idea to monitor which users are assigned to the different roles to meet requirements, such as Monitoring, Audit and Security rules. This information can be logged into a database or something like this.

Report Server

With the Power BI Report Server (on premises) it is easy. You have two options, you can use with PowerShell the REST API (https://docs.microsoft.com/de-de/power-bi/report-server/rest-api) or query the Report Server DB with T-SQL. Last one, is in my point of view, easier and more useful. So, if you use following query and assign users to roles:

SELECT 
       catalog.Path
       , dbo.Catalog.name
       , datamodelrole.ModelRoleName
       , users.UserName
  FROM [ReportServer].[dbo].[UserDataModelRole]
  INNER JOIN dbo.DataModelRole ON DataModelRole.DataModelRoleID = UserDataModelRole.DataModelRoleID
  INNER JOIN dbo.Users ON Users.UserID = UserDataModelRole.UserID
  INNER JOIN dbo.Catalog ON Catalog.ItemID = DataModelRole.ItemID

You get all users which are assigned to the different roles of each report are using RLS.

Power BI online

With Power BI online it is more difficult. Because the users are managed by the portal, but the assignments are saved inside the OLAP cube (every dataset is a OLAP cube in behind). If you want to query the users, you need a Premium or PPU capacity assigned to the workspaces. With this license, you can connect to the dataset / or cube by using tools like the DAX Studio. Here you can use this  query to get the users of the cube. You don’t have the possibility to query all datasets at once. There is no PowerShell or REST API command available to do this. If you want to do this, you can iterate over your workspaces

SELECT * FROM $System.TMSCHEMA_ROLE_MEMBERSHIPS

To determine which dataset has a RLS configured, you can check this by using a REST API call:

But, if you download and upload the PBIX file and upload it with another name, the role assignment is lost. The assigned users are not stored inside the dataset! A little bit confusing, but the reason is very simple, Power BI files doesn’t store the assignments, a OLAP cube will do that. So, if you want to migrate a report, check the REST API, get  the assignments, and re-assign the users.

Categorized: Allgemein

Comments are closed.