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

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:

1. Register App in Entra ID

2. Create Client Secret

3. Add API permissions:

   • Dataset.Read.All

   • Workspace.Read.All

4. Grant Admin Consent

5. Enable SPN usage in Power BI Tenant Settings

6. Add SPN as:

   • Workspace Member/Admin

   • Or Dataset Admin

📊 Final Architecture

1. REST API → Build inventory table of:

   • Workspace

   • Dataset

2. Loop datasets → XMLA connection

3. Execute DMV queries

4. Store results in:

   • SQL table

   • Dataflow

   • Lakehouse

   • or Power BI model

🚀 Summary

Requirement Needed?

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 🙂