Power BI and PowerShell: add RLS roles in bulk

21 Nov, 2025

This is a method to add RLS roles in bulk based on a source file. This is using a PowerShell script to directly edit the model.bim file to write in the roles and all of the rules.

Also included is a function to clear all of the roles. This is required if anything goes wrong with the import. This also means that all of the security roles needed on the model needs to be defined in the import file.

Because all of the DAX code comes from the source file, there is no syntax validation. Make sure that all of the code to be inputted is correct before writing it in.

The source file has the following structure:

role	name	filterExpression
This is the name of the security role	This is the name of the table. Roles with rules on multiple tables will need multiple lines	Ths is the DAX code that is to be written in

Code was generated by Claude Sonnet 4.5.

function Add-RLSRolesToModel ( [string]$ModelJsonPath, [PSCustomObject[]]$RolesData ) {
    
    # Read model.json
    $model = Get-Content $ModelJsonPath -Raw | ConvertFrom-Json -Depth 100
    
    # Ensure roles array exists
    if (-not $model.model.roles) {
        $model.model | Add-Member -NotePropertyName 'roles' -NotePropertyValue @()
    }
    
    # Group roles data by role name
    $groupedRoles = $RolesData | Group-Object -Property role

    # Add each role
    foreach ($roleGroup in $groupedRoles) {
        # Create table permissions array for this role
        $tablePermissions = @()
        foreach ($entry in $roleGroup.Group) {
            $tablePermObj = [PSCustomObject]@{
                name = $entry.name
            }

            # Only add filterExpression if it exists
            if ($entry.filterExpression) {
                $tablePermObj | Add-Member -NotePropertyName 'filterExpression' -NotePropertyValue $entry.filterExpression
            }

            $tablePermissions += $tablePermObj
        }

        # Create the role object with all its table permissions
        $roleObject = [PSCustomObject]@{
            name = $roleGroup.Name
            modelPermission = "read"
            tablePermissions = $tablePermissions
        }

        $model.model.roles += $roleObject
    }

    $model.model.roles
    
    # Write back
    $json = $model | ConvertTo-Json -Depth 100

    # $json
    [System.IO.File]::WriteAllText($ModelJsonPath, $json, [System.Text.UTF8Encoding]::new($false))
}

# Generated with Claude Code (Sonnet 4.5 - claude-sonnet-4-5-20250929) - 2025-11-07
function Remove-AllRolesFromModel ( [string]$ModelJsonPath ) {

    # Read model.json
    $model = Get-Content $ModelJsonPath -Raw | ConvertFrom-Json -Depth 100

    # Get all existing roles for reporting
    $existingRoles = @()
    if ($model.model.roles) {
        $existingRoles = $model.model.roles | ForEach-Object { $_.name }
        Write-Host "Found $($existingRoles.Count) roles to delete: $($existingRoles -join ', ')"
    } else {
        Write-Host "No roles found in model."
        return
    }

    # Remove all roles by setting to empty array
    $model.model.roles = @()

    Write-Host "All roles deleted from model."

    # Write back
    $json = $model | ConvertTo-Json -Depth 100
    [System.IO.File]::WriteAllText($ModelJsonPath, $json, [System.Text.UTF8Encoding]::new($false))

    Write-Host "Model saved successfully."
}