Forum Discussion

New Member

6 months ago

How to extract RLS (roles, filters...)from Power BI Service datasets step-by-step(REST API/XMLA/DMV?

Hi, I’m new to Power BI APIs and I need help with a step-by-step approach. Goal: build a table/report that lists the RLS configured in the Power BI Service for all published semantic models (datase...

Need Help

bariscihan

Resolver II

6 months ago

Hi,

You can achieve this, but you need to combine Power BI REST API (for inventory) and XMLA endpoint + DMVs (for RLS metadata). Below is a practical step-by-step approach that I’ve used successfully.

✅ Step 1 – List Workspaces (REST API)

Call:

GET https://api.powerbi.com/v1.0/myorg/groups

Required scope:

Workspace.Read.All (or ReadWrite)

This returns all workspaces the principal has access to.

✅ Step 2 – List Datasets per Workspace

For each workspace:

GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets

Required scope:

Dataset.Read.All

At this point you have:

Workspace ID
Dataset ID
Dataset Name

⚠️ Important Requirement

To extract RLS roles and filters, the dataset must:

Be in Premium / Fabric capacity
Have XMLA endpoint enabled (Read or Read/Write)

Otherwise DMVs will not work.

✅ Step 3 – Connect to XMLA Endpoint

XMLA endpoint format:

powerbi://api.powerbi.com/v1.0/myorg/{WorkspaceName}

You can connect using:

SSMS
Tabular Editor
PowerShell
Python (via ADOMD)
C# (Microsoft.AnalysisServices.Tabular)

Authentication:

User (Azure AD)
OR Service Principal (must be enabled in tenant settings)

Tenant setting required:

“Allow service principals to use Power BI APIs”
“Allow XMLA endpoints and Analyze in Excel with on-prem datasets”

✅ Step 4 – DMV Queries for RLS

Once connected to the dataset database, run:

🔹 List Roles

SELECT * 
FROM $SYSTEM.TMSCHEMA_ROLES

🔹 Role Members

SELECT * 
FROM $SYSTEM.TMSCHEMA_ROLE_MEMBERSHIPS

🔹 Table-Level Filters (RLS expressions)

SELECT * 
FROM $SYSTEM.TMSCHEMA_TABLE_PERMISSIONS

The column FilterExpression contains the DAX filter definition.

✅ Optional – Python Example (Simplified Concept)

Using pyadomd:

from pyadomd import Pyadomd

conn_str = "Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/WorkspaceName;Initial Catalog=DatasetName;"

with Pyadomd(conn_str) as conn:
    with conn.cursor().execute("SELECT * FROM $SYSTEM.TMSCHEMA_ROLES") as cur:
        print(cur.fetchall())

(You must authenticate beforehand using Azure AD token.)

🔐 Service Principal Requirements

If using SPN:

Register App in Entra ID
Create Client Secret
Add API permissions:
- Dataset.Read.All
- Workspace.Read.All
Grant Admin Consent
Enable SPN usage in Power BI Tenant Settings
Add SPN as:
- Workspace Member/Admin
- Or Dataset Admin

📊 Final Architecture

REST API → Build inventory table of:
- Workspace
- Dataset
Loop datasets → XMLA connection
Execute DMV queries
Store results in:
- SQL table
- Dataflow
- Lakehouse
- or Power BI model

🚀 Summary

Requirement Needed?

Premium/Fabric capacity	✅ Yes
XMLA endpoint enabled	✅ Yes
REST API	✅ For inventory
DMVs	✅ For roles/members/filters
Service Principal	Optional but recommended for automation

This approach gives you:

Workspace → Dataset → Role → Members → Filter Expression

If helpful, I can also share a fully automated PowerShell or end-to-end architecture pattern.

Hope this helps 🙂

Anonymous
Not applicable
6 months ago
Thanks again — this was very helpful.
We’ve confirmed:
Premium/Fabric capacity is available
XMLA endpoint is enabled
I understand the overall architecture now:
Use REST API to inventory workspaces and datasets
Loop datasets and connect via XMLA
Query:
$SYSTEM.TMSCHEMA_ROLES
$SYSTEM.TMSCHEMA_ROLE_MEMBERSHIPS
$SYSTEM.TMSCHEMA_TABLE_PERMISSIONS
Before implementing automation, I’d appreciate seeing your PowerShell (or Python) end-to-end pattern for:
Service principal authentication
XMLA connection
Executing DMV queries programmatically
Thanks again!
- v-nmadadi-msft
  Community Support
  6 months ago
  Hi matesum1234 ,
  Thanks for reaching out to the Microsoft Fabric Community forum.
  
  Install Power BI module using
  
  Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser -Force
  
  Provide information related to Service principal
  
  $AppId = "examplexxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" $TenantId = "examplexxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" $ClientSecret = "ClientSecretHere"
  
  Create Secure Strings
  
  $SecurePassword = ConvertTo-SecureString $ClientSecret -Force -AsPlainText $Credential = New-Object Management.Automation.PSCredential($AppId, $SecurePassword)
  
  Connect to the Power BI service using these commands
  
  Connect-PowerBIServiceAccount -ServicePrincipal -TenantId $TenantId -Credential $Credential
  
  get the list of workspaces and list of reports using these commands
  
  Get-PowerBIWorkspace Get-PowerBIReport
  
  To connect to XMLA Endpoint:
  
  $tokenResponse = Invoke-RestMethod ` -Method Post ` -Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" ` -Body $body $accessToken = $tokenResponse.access_token
  
  Add-Type -AssemblyName "Microsoft.AnalysisServices.Tabular" $workspaceName = "your workspace name" $xmlaEndpoint = "powerbi://api.powerbi.com/v1.0/myorg/$workspaceName" $server = New-Object Microsoft.AnalysisServices.Tabular.Server # Connection string with access token $connectionString = "DataSource=$xmlaEndpoint;Password=$accessToken;User ID=app:$clientId@$tenantId" $server.Connect($connectionString)
  
  List database using
  
  $server.Databases | Select Name
  
  Each database = one semantic model (dataset).
  
  Run DMV queries using
  
  $datasetName = "Your Dataset Name" $database = $server.Databases[$datasetName] $query = "SELECT * FROM `$SYSTEM.TMSCHEMA_ROLES" $result = $database.Model.ExecuteDaxQuery($query) $result.Tables[0]
  
  Reference:
  Working with PowerShell in Power BI | Microsoft Power BI Blog | Microsoft Power BI
  Connect-PowerBIServiceAccount (MicrosoftPowerBIMgmt.Profile) | Microsoft Learn
  
  I hope this information helps. Please do let us know if you have any further queries.
  Thank you
  - Anonymous
    Not applicable
    6 months ago
    Do you know if that error is happening because I am not the creator of the semantic model? I need a solution to extract the RLS information without being the creator. AdomdErrorResponseException: User '<my_email>' needs to be an administrator to read the metadata of the database '6b4e132c-...'
Anonymous
Not applicable
6 months ago
Hi,
I am developing an audit tool in Microsoft Fabric to automatically extract RLS configurations (Roles, Members, and DAX Filter Expressions) from several semantic models.
I am using a Python Notebook with the sempy.fabric library (Semantic Link) to query the DMVs. While the code works perfectly on models I have created myself, I encounter a security blocking issue when running it against production models where I am an Administrator but not the Original Creator (Owner).
The Code:
import sempy.fabric as fabric
import pandas as pd
workspace_id = "..."
# This model was created by a colleague, but I am an Admin of the Workspace
modelo_nombre = "..."
try:
# Querying the DMVs for Roles, Memberships and Permissions
df_roles = fabric.evaluate_dax(modelo_nombre, "SELECT [ID], [Name] FROM $SYSTEM.TMSCHEMA_ROLES", workspace=workspace_id)
df_usuarios = fabric.evaluate_dax(modelo_nombre, "SELECT [RoleID], [MemberName] FROM $SYSTEM.TMSCHEMA_ROLE_MEMBERSHIPS", workspace=workspace_id)
df_reglas = fabric.evaluate_dax(modelo_nombre, "SELECT [RoleID], [TableID], [FilterExpression] FROM $SYSTEM.TMSCHEMA_TABLE_PERMISSIONS", workspace=workspace_id)

print("Audit successful")
except Exception as e:
print(f"Error: {e}")

he Error:
When I run this, I get the following exception:
AdomdErrorResponseException: User '<my_email>' needs to be an administrator to read the metadata of the database '6b4e132c-...'
Environment & Context:
Capacity: Fabric F64 (Premium).
XMLA Endpoint: Set to "Read Write".
My Permissions: I am a Workspace Administrator and I have explicit Build permissions on the dataset.
Observation: If the Original Creator runs the exact same code, it works perfectly.
My Question:
My goal is to create a universal audit script that can be executed by any authorized auditor/admin without requiring them to be the "Owner" of every semantic model.
Is there a specific Tenant setting or Analysis Services permission that prevents a Workspace Admin from reading DMVs via XMLA if they are not the Owner?
Is there a way to bypass this "Ownership" requirement (perhaps using a Service Principal or a different connection method)?
Why does the engine return a "needs to be an administrator" error when I already have the Admin role in the workspace?
I am looking for a solution that allows for a centralized audit of RLS across the entire organization.
Thanks for your help!