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

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-...'

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

    Hi matesum1234 ,

    The error is not related to being the creator of the semantic model. XMLA metadata queries such as TMSCHEMA_ROLES require database-level administrative permissions. To extract RLS definitions, you must be a Workspace Admin. Member or Build permissions are not sufficient to read model metadata via XMLA.

    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

      Hello, I’m getting the error “needs to be an administrator to read the metadata of the database”, even though I am already a workspace administrator. What additional permissions do I need in this case?

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

        Hi matesum1234 ,
        Ideally it should work with just Workspace admin role, no additional permissions are required. Please consider reaching out to Microsoft Support. You can provide them with all the troubleshooting steps you've already taken, which will help them understand the issue better and provide a resolution. They might be able to identify something specific about your admin account setup or provide a solution that isn't immediately obvious. 

        Below is the link to create Microsoft Support ticket:
        Create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn

        Thank you