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 ( 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:

       , 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


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.

Continuous Deployment of Azure Analysis Services Cubes – Part 2; SQL Authentication

In my last blog post, I’ve described how you can deploy SSAS cubes to different stages with the Tabular Editor by using a OATUH authentication for the data source. This post describes a cube by using a SQL authenticated connection. Firstly, I’ve created a cube by using a SQL Auth connection. Visual Studio does not save the password for the connection inside the BIM file. But you can add this, by adding this parameter inside the BIM file:

But you must awa43e of, that the password is stored as plain text inside your code. You don’t have the possibility to encrypt it. After deploying the cube, I created s pipeline inside Azure DevOps like the other from Part 1.

The opposite to the other pipeline is, that we have a step to edit the data source.

This PowerShell script will add the password to the connection string:

$AlterDatasourceCommand = @"
  "createOrReplace": {
    "object": {
      "database": "Cube",
      "dataSource": "SQL/sqlpl001 database windows net;sql01"
    "dataSource": {
      "type": "structured",
      "name": "SQL/sqlpl001 database windows net;sql01",
      "connectionDetails": {
        "protocol": "tds",
        "address": {
          "server": "",
          "database": "sql01"
        "authentication": null,
        "query": null
      "credential": {
        "AuthenticationKind": "UsernamePassword",
        "kind": "SQL",
        "path": ";sql01",
        "Username": "ok",
		"Password": "XXXXX",
        "EncryptConnection": true

$myPassword = ‘XXXXX'
$myUsername = ''

$password = ConvertTo-SecureString $myPassword -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($myUsername, $password)

Invoke-ASCmd -Server asazure:// -Database Cube -Query $AlterDatasourceCommand -Credential $credential

If you need to script your connection, you can right click to your connection and choose the Replace option. But here you must also add the password option.

Continuous Deployment of Azure Analysis Services Cubes – Part 1

With Tabular Editor from Daniel Otykier, you can easily deploy tabular models to different stages. This Blog post covers how to do that and how to manage the connection which points to the correct stage. The second part will cover, how to do that with a SQL Authenticated user.
This blog post follows this series:

Firstly, you need a pipeline with three steps: Download the portable version of tabular editor, the preparing of the release which will deployed, and the final deployment. The cube uses an OAuth connection which also must be maintained in the last step.

The first step downloads the Tabular Editor and extracts the content to the environment:

# Download URL for Tabular Editor portable:
$TabularEditorUrl = "" 

# Download destination (root of PowerShell script execution path):
$DownloadDestination = join-path (get-location) ""

# Download from GitHub:
Invoke-WebRequest -Uri $TabularEditorUrl -OutFile $DownloadDestination

# Unzip Tabular Editor portable, and then delete the zip file:
Expand-Archive -Path $DownloadDestination -DestinationPath (get-location).Path
Remove-Item $DownloadDestination

The second step copies the BIM file and replaces the connection. Normally you have different branches, but in this case it is easier. The connection from the BIM file points to the DEV SQL database, the replacement points the cube to the testing database.

Copy-Item "_cube/cube/cube/Model.bim" -Destination "_cube/cube/cube/Test.bim" -Force

(Get-Content "_cube/cube/cube/Test.bim") -replace '"server": "",', '"server": "",' | Set-Content "_cube/cube/cube/Test.bim"

The last step deploys the cube to the testing environment. The password of the deployment user is stored inside a variable and can get from KeyVault ( )

TabularEditor.exe _cube\cube\cube\Test.bim  -D asazure:// Cube -L $(password) -O

But when you want to refresh the cube to get new data, you get an error. The reason why is, that you OAuth connection user doesn’t have actual credential information:

You can fix it, by refreshing the credentials. But you must be aware of, that your credentials will expire.

After that, and when you refresh the cube instantly, the lease time will be updated.