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 (datasets), including:

Workspace

Dataset / semantic model

RLS role name

Role members (users/groups)

RLS filter expressions (if possible)

What I know so far (but I don’t know how to implement it)

There is the Power BI REST API to list workspaces and datasets.

People mention using XMLA endpoint + DMVs to read roles/members.

What I need from you (step-by-step)

What exact REST API calls should I use to get the list of:

workspaces

datasets per workspace

Once I have the dataset, how do I connect to the XMLA endpoint (example code/script)?

preferably a simple example (PowerShell / Python / C#)

Which DMV queries return:

roles

members

filter expressions

What permissions/capacity requirements do I need for this to work?

If I use a service principal, what are the exact prerequisites in Entra ID / Power BI tenant settings?

I’m basically looking for a beginner-friendly “do this, then this” guide or working sample.

Thanks!

Need Help

10 Replies

  • 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

      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's avatar
        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'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!

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    5 months ago

    Hi matesum1234 ,

    Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

    Thank you for your understanding and assistance.