Retreive the use of Row-level security

[jlwkok]

Can you get this data for all reports at once from the PBIRS database or via a PowerShell request?

This is regarding more than 400 reports and that's why I'm looking for an automated solution/query.

 

[sarada · Accepted solution]

powershell script you can use to get rls for one report:

$ReportServerUri = "https://<server_name>/PBIReports
#API to get roles and role assignments
$RoleAssignmentsPath = $ReportServerUri + "/api/v2.0/PowerBIReports(Path='<ReportPath>')/DataModelRoleAssignments"
$RolesPath = $ReportServerUri + "/api/v2.0/PowerBIReports(Path='<ReportPath>')/DataModelRoles"

#call API - row level security
$RoleAssignments = Invoke-RestMethod -Uri $RoleAssignmentsPath -ContentType "application/json" -UseDefaultCredentials -Method Get
#call API - RLS role
$Roles = Invoke-RestMethod -Uri $RolesPath -ContentType "application/json" -UseDefaultCredentials -Method Get

note that in above script if your report is in root folder of PBIRS portal then  <ReportPath>  is /report_name

hope this fix your issue!   

[jlwkok]

Great, thank you! With your example I was able to make below loop and got all of them.

# Define variables

$webPortalURL = "https://example.com/ReportsPowerBI"

$PBI_Reports_Array = @()

$PBI_Reports_Array = $(Invoke-RestMethod -UseDefaultCredentials -uri $($webPortalURL + "/api/v2.0/PowerBIReports"))

$loopCount = 0 

$RoleAssignments = @()

# Loop through all Power BI reports 

foreach ($reportPath in $PBI_Reports_Array.value.path) {

    try {

$RoleAssignmentsPath = $ReportServerUri + "/api/v2.0/PowerBIReports(Path='" + $reportPath + "')/DataModelRoleAssignments";

$RolesPath = $ReportServerUri + "/api/v2.0/PowerBIReports(Path='" + $reportPath + "')/DataModelRoles";

#call API - row level security

$RoleAssignments += $(Invoke-RestMethod -Uri $RoleAssignmentsPath -ContentType "application/json" -UseDefaultCredentials -Method Get);

#call API - RLS role

#$Roles = Invoke-RestMethod -Uri $RolesPath -ContentType "application/json" -UseDefaultCredentials -Method Get

        if ([string]::IsNullOrEmpty($($RoleAssignments[$loopCount].value))) { 

            write-host "$loopCount | $reportPath | No DataModelRoleAssignments for this report!"; 

        }

        else {

            write-host "$loopCount | $reportPath | $($RoleAssignments[$loopCount].value.GroupUserName)" -foregroundcolor magenta;

        }

        $loopCount++;

    }

    catch {

    }

}

[v-binbinyu-msft]

Hi @jlwkok ,

You configure RLS for reports imported into Power BI with Power BI Desktop. You can also configure RLS on reports that use DirectQuery, such as SQL Server. Keep in mind that RLS isn't respected if your DirectQuery connection uses integrated authentication for report readers. For Analysis Services live connections, you configure row-level security on the on-premises model. The security option doesn't show up for live connection datasets.

For more details, you could read related document: Row-level security (RLS) in Power BI Report Server - Power BI | Microsoft Learn

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.