How to Get Table → Security Role Mapping in Power Platform
Power Platform |
Dataverse | Security & Governance
When auditing your Dataverse environment, one of the most
common questions is: "Which security roles have access to this
table?" The Power Platform UI doesn't give you a direct answer — you'd
have to open each role one by one. This post shows you how to do it properly
using PowerShell and the Dataverse Web API.
Why This Matters
Security role misconfiguration is one of the top causes of
data exposure in Dataverse-based apps. Before going live with any Power Pages
or model-driven app, you should know exactly which roles can read, write,
create, or delete records in every table — especially custom ones.
The default Admin Center view only shows you “what tables does
this role access?” — not the reverse. For governance audits, you need the
opposite: given a table, which roles touch it?
Prerequisites
•
Windows PowerShell 5.1 (not PowerShell Core/7 — the
module is incompatible)
•
Microsoft.Xrm.Data.PowerShell module installed
•
System Administrator or equivalent role in the target
environment
⚠️ Run all commands in
Windows PowerShell 5.1. If you open PowerShell 7 / pwsh, you will get:
"module is not compatible with PowerShell Core".
Step 1 — Install and Connect
Open Windows PowerShell as Administrator and run:
Install-Module -Name Microsoft.Xrm.Data.PowerShell -Scope
CurrentUser -Force -AllowClobber
Import-Module Microsoft.Xrm.Data.PowerShell
$conn = Get-CrmConnection -InteractiveMode
This opens an interactive login window. Sign in with your
Dataverse admin account. After login, verify the connection:
$conn.ConnectedOrgFriendlyName
Step 2 — Full Mapping Script
The module's cmdlets don't include a direct
Get-CrmEntityPrivileges function, so we use the Dataverse Web API via
Invoke-RestMethod. The script below does a complete table → role lookup in four
stages.
$tableName = "account" # Change to your table logical name
$orgUrl =
"https://<yourorg>.crm.dynamics.com"
Import-Module Microsoft.Xrm.Data.PowerShell
$conn =
Get-CrmConnection -InteractiveMode
$token =
$conn.CurrentAccessToken
$headers = @{
"Authorization" =
"Bearer $token"
"OData-MaxVersion" = "4.0"
"OData-Version" =
"4.0"
"Accept" =
"application/json"
}
# STEP 1 - Get privileges for the table
$privUrl =
"$orgUrl/api/data/v9.2/privileges?`$filter=contains(name,'$tableName')&`$select=name,privilegeid"
$privileges = (Invoke-RestMethod -Uri $privUrl -Headers $headers
-Method Get).value
Write-Host "Privileges found: $($privileges.Count)"
# STEP 2 - Get all security roles
$roles = (Invoke-RestMethod -Uri
"$orgUrl/api/data/v9.2/roles?`$select=name,roleid" -Headers $headers
-Method Get).value
Write-Host "Roles found: $($roles.Count)"
# STEP 3 - Check each role for table privileges
$results = @()
foreach ($role in $roles) {
$detail = Invoke-RestMethod -Uri
"$orgUrl/api/data/v9.2/roles($($role.roleid))?`$expand=roleprivileges_association(`$select=name)"
-Headers $headers -Method Get
$matchedPrivs =
$detail.roleprivileges_association | Where-Object { $_.name -like
"*$tableName*" }
foreach ($priv in
$matchedPrivs) {
$results +=
[PSCustomObject]@{
Table = $tableName
Role = $role.name
Privilege =
$priv.name
}
}
}
# STEP 4 - Display and export
$results | Sort-Object Role | Format-Table -AutoSize
$results | Export-Csv -Path
".\RoleMapping_$tableName.csv" -NoTypeInformation
Write-Host "Exported to RoleMapping_$tableName.csv"
Sample Output
|
Table |
Role |
Privilege |
|
account |
Sales Manager |
prvReadaccount |
|
account |
Sales Manager |
prvWriteaccount |
|
account |
System Administrator |
prvReadaccount |
|
account |
System Administrator |
prvCreateaccount |
|
account |
Custom Role A |
prvDeleteaccount |
Alternative — FetchXML (Faster for Large Environments)
If you have many security roles, the loop above can be slow.
Use FetchXML instead for a single-query approach:
$fetchXml = @"
<fetch>
<entity
name="role">
<attribute
name="name" />
<link-entity
name="roleprivileges" from="roleid" to="roleid"
intersect="true">
<link-entity
name="privilege" from="privilegeid"
to="privilegeid">
<attribute
name="name" />
<filter>
<condition
attribute="name" operator="like"
value="%account%" />
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>
"@
$encoded = [System.Web.HttpUtility]::UrlEncode($fetchXml)
$result =
Invoke-RestMethod -Uri
"$orgUrl/api/data/v9.2/roles?fetchXml=$encoded" -Headers $headers
-Method Get
$result.value | Select-Object name | Sort-Object name |
Format-Table -AutoSize
💡 Tip: You can
also paste this FetchXML directly into XrmToolBox → FetchXML Builder for a
no-code alternative.
Troubleshooting
|
Error |
Fix |
|
Module not compatible with
PowerShell Core |
Switch to Windows
PowerShell 5.1 |
|
CurrentAccessToken is null |
Reconnect using
Get-CrmConnection -InteractiveMode |
|
401 Unauthorized on API
calls |
Token expired — reconnect
and re-run |
|
Empty results |
Use the table's logical
name, e.g. cr123_mytable not display name |
|
Slow execution |
Switch to the FetchXML
approach |
Wrapping Up
The Power Platform Admin Center gives you role-centric views,
but for governance audits you often need a table-centric view. By combining the
Microsoft.Xrm.Data.PowerShell module for authentication with Dataverse Web API
calls, you can build a clean, exportable mapping of every security role that
has privileges on any given table.
The exported CSV is also useful as documentation for ALM
pipelines, change management reviews, and client handoffs — especially in
enterprise projects where compliance teams need to sign off on data access
controls.
#PowerPlatform #Dataverse
#PowerShell #SecurityRoles #MicrosoftDataverse #PowerApps
#ALM #Governance #Microsoft365
#LowCode
#EnterpriseArchitecture
#PowerPlatformDev
#DataverseWebAPI
#PowerPlatformAdmin
No comments:
Post a Comment