Checking Dataverse Table Permissions using PowerShell

In Microsoft Dataverse, table permissions determine who can read, write, update, delete, or share records. PowerShell allows administrators to efficiently retrieve and audit permissions for tables, ensuring proper access control.

This guide explains how to check Dataverse table permissions using PowerShell, covering:
Connecting to Dataverse
Retrieving tables
Checking permissions for security roles
Exporting permissions for auditing

Step 1: Prerequisites

1. Required Permissions

System Administrator or Power Platform Admin access.
Dataverse API enabled.

2. Install and Import Required Modules

Ensure you have the Power Platform and Dataverse PowerShell modules installed.

# Install Power Platform Administration module
Install-Module -Name Microsoft.PowerPlatform.Administration -Scope CurrentUser -Force

# Install Dataverse Client module
Install-Module -Name Microsoft.PowerPlatform.Cds.Client -Scope CurrentUser -Force

# Import the modules
Import-Module Microsoft.PowerPlatform.Administration
Import-Module Microsoft.PowerPlatform.Cds.Client

Step 2: Connect to Dataverse

Option 1: Interactive Login

# Connect to Dataverse interactively
$connection = Connect-CdsService -ConnectionString "AuthType=OAuth;Url=https://yourorg.crm.dynamics.com;Prompt=Login"

A sign-in window will appear for authentication.

Option 2: Using Service Principal (App Registration)

For automation scripts, use an Azure AD App Registration.

# Define credentials
$clientId = "your-app-client-id"
$clientSecret = "your-app-client-secret"
$tenantId = "your-tenant-id"
$orgUrl = "https://yourorg.crm.dynamics.com"

# Convert secret to secure string
$secureSecret = ConvertTo-SecureString $clientSecret -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($clientId, $secureSecret)

# Connect to Dataverse
$connection = Connect-CdsService -Url $orgUrl -ClientId $clientId -ClientSecret $secureSecret -TenantId $tenantId

Step 3: Retrieve All Tables in Dataverse

# Fetch all tables
$tables = Get-CdsRecord -Connection $connection -EntityLogicalName "entity"

# Display table names
$tables | Select-Object logicalname, displayname

This retrieves all tables (entities) in Dataverse.

Step 4: Retrieve Security Roles and Table Permissions

1. Get All Security Roles

# Fetch all security roles
$roles = Get-CdsRecord -Connection $connection -EntityLogicalName "role"

# Display roles
$roles | Select-Object roleid, name

2. Check Role-Based Permissions on a Specific Table

# Define table name and security role
$tableName = "account"   # Change to your table
$roleName = "Basic User"

# Get Role ID
$role = Get-CdsRecord -Connection $connection -EntityLogicalName "role" -Filter "name eq '$roleName'"
$roleId = $role.roleid

# Get Table Permissions for the Role
$permissions = Get-CdsRecord -Connection $connection -EntityLogicalName "privilege" -Filter "objecttypecode eq '$tableName' AND roleid eq '$roleId'"

# Display permissions
$permissions | Select-Object privilegeid, name, accessright

Step 5: Check a User’s Table Permissions

# Define user email
$userEmail = "user@example.com"
$tableName = "account"

# Get User ID
$user = Get-CdsRecord -Connection $connection -EntityLogicalName "systemuser" -Filter "internalemailaddress eq '$userEmail'"
$userId = $user.systemuserid

# Get Roles Assigned to User
$userRoles = Get-CdsRecord -Connection $connection -EntityLogicalName "systemuserroles" -Filter "systemuserid eq '$userId'"

# Loop through roles and fetch permissions
foreach ($role in $userRoles) {
    $roleId = $role.roleid
    $permissions = Get-CdsRecord -Connection $connection -EntityLogicalName "privilege" -Filter "objecttypecode eq '$tableName' AND roleid eq '$roleId'"

    Write-Host "Permissions for $userEmail on $tableName:"
    $permissions | Select-Object name, accessright
}

Step 6: Export Table Permissions to a CSV File

# Define export path
$csvFilePath = "C:\Dataverse_Export\TablePermissions.csv"

# Fetch all security roles
$roles = Get-CdsRecord -Connection $connection -EntityLogicalName "role"

# Initialize permission list
$permissionList = @()

# Loop through each role
foreach ($role in $roles) {
    $roleId = $role.roleid
    $roleName = $role.name

    # Fetch table permissions for the role
    $permissions = Get-CdsRecord -Connection $connection -EntityLogicalName "privilege" -Filter "roleid eq '$roleId'"

    foreach ($permission in $permissions) {
        $tableName = $permission.objecttypecode
        $accessRights = $permission.accessright

        $permissionList += [PSCustomObject]@{
            RoleName = $roleName
            TableName = $tableName
            AccessRights = $accessRights
        }
    }
}

# Export to CSV
$permissionList | Export-Csv -Path $csvFilePath -NoTypeInformation -Encoding UTF8

Write-Host "Table permissions exported to $csvFilePath"

Step 7: Disconnect from Dataverse

Disconnect-CdsService -Connection $connection
Write-Host "Disconnected from Dataverse."