Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
8 | |
5 | |
2 | |
2 |
User | Count |
---|---|
13 | |
7 | |
6 | |
2 | |
2 |