Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
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