Forum Discussion

matesum1234's avatar
matesum1234
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's avatar
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:

  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?

Premium/Fabric capacity✅ Yes
XMLA endpoint enabled✅ Yes
REST API✅ For inventory
DMVs✅ For roles/members/filters
Service PrincipalOptional 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's avatar
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!