Update data model role assignment (RLS) of PBI report using Powershell

sarada — Fri, 26 Apr 2024 03:09:08 GMT

Hi,

I need to dynamically update Row level security of power bi report that is deployed to PBIRS. I have used powershell script to get list of users in row level security but I could not find out how to update the list or add users to RLS.

here is the step that I have done:

$PBIRSUrl = 'https://MyPBIRServer.com'

#API to get role assignments
$reportRoles = $PBIRSUrl + "/PBIReports/api/v2.0/PowerBIReports(Path='/MyReport')/DataModelRoleAssignments"

#call API
$result = Invoke-RestMethod -Uri $reportRoles -ContentType "application/json" -UseDefaultCredentials -Method Get

#steps to update $result.value and add new user then save it to $roleAssgnmtjson

$roleAssgnmtjson

@{[
{
"GroupUserName": "domain\User001",
"DataModelRoles": ["2336b4a-e5b0-49ef-8556-62152375f005"]
},
{
"GroupUserName": "domain\User002",
"DataModelRoles": ["2336b4a-e5b0-49ef-8556-62152375f005"]
}

]}

#API to update role assignments

Invoke-RestMethod -Uri $reportRoles -ContentType "application/json" -Body $roleAssgnmtjson -UseDefaultCredentials -Method Put

Invoke-RestMethod : The remote server returned an error: (400) Bad Request.
At line:1 char:1
+ Invoke-RestMethod -Uri $reportRoles -ContentType "application/json" ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

does anyone know how to fix the issue?

I have also tried using $resultjson for body of put request, but same error

$resultjson:

{
"@odata.context": "https://MyPBIRServer.com/PBIReports/api/v2.0/$metadata#Collection(Model.DataModelRoleAssignment)",
"value": [
{
"GroupUserName": "domain\User001",
"DataModelRoles": "2336b4a-e5b0-49ef-8556-62152375f005"
},
{
"GroupUserName": "domain\User002",
"DataModelRoles": "2336b4a-e5b0-49ef-8556-62152375f005"
}
]}

Thanks

Re: Update data model role assignment (RLS) of PBI report using Powershell

sarada — Mon, 29 Apr 2024 00:46:06 GMT

Issue here was about body of request. I have tested the api using Postman and found out body of my resquest is not correct in PUT request. correct format of

$roleAssgnmtjson:

[
{
"GroupUserName": "domain\User001",
"DataModelRoles": ["2336b4a-e5b0-49ef-8556-62152375f005"]
},
{
"GroupUserName": "domain\User002",
"DataModelRoles": ["2336b4a-e5b0-49ef-8556-62152375f005"]
}

]
one point here is that 'DataModelRoles' is an array and I was trying to update that as text for new users: {
"GroupUserName": "domain\User002",
"DataModelRoles": "2336b4a-e5b0-49ef-8556-62152375f005"
}
this was another issue that I didn't notice because I had only one row level security in my report.
hope this help others with same issue

topic Update data model role assignment (RLS) of PBI report using Powershell in Report Server

Update data model role assignment (RLS) of PBI report using Powershell

Re: Update data model role assignment (RLS) of PBI report using Powershell