Row-Level Security (RLS) in Power BI allows you to restrict data access for different users based on roles. Using PowerShell, you can automate RLS role assignments, making it easier to manage security at scale.
This guide will walk you through configuring and managing RLS in Power BI using PowerShell.
Step 1: Prerequisites
1. Install the Power BI Management Module
Ensure the Microsoft Power BI Management module is installed:
Install-Module MicrosoftPowerBIMgmt -Scope CurrentUser -Force
Power BI PowerShell module installed!
2. Connect to Power BI Service
Authenticate using an admin account:
Connect-PowerBIServiceAccount
Authenticated successfully!
Step 2: Verify Available Power BI Datasets
RLS is applied to datasets in Power BI. To get a list of datasets, run:
Get-PowerBIDataset -Scope Organization | Select-Object Id, Name, WorkspaceId
Power BI datasets listed!
Step 3: Retrieve Existing RLS Roles
To check the RLS roles in a dataset:
Get-PowerBIDatasetRlsRole -DatasetId "<Dataset_ID>" -WorkspaceId "<Workspace_ID>"
RLS roles retrieved!
Step 4: Assign Users to RLS Roles
To assign a user to a specific RLS role, use:
Add-PowerBIDatasetRlsRoleMember -DatasetId "<Dataset_ID>" -WorkspaceId "<Workspace_ID>" -Role "SalesManager" -Member "user@yourdomain.com"
User added to RLS role!
Step 5: Assign Multiple Users from a CSV File
To bulk assign users to RLS roles, create a CSV (RLS_Users.csv):
UserPrincipalName,Role
user1@yourdomain.com,SalesManager
user2@yourdomain.com,Finance
user3@yourdomain.com,HR
Then, use PowerShell:
$workspaceId = "<Workspace_ID>"
$datasetId = "<Dataset_ID>"
$users = Import-Csv "C:\Path\To\RLS_Users.csv"
foreach ($user in $users) {
Add-PowerBIDatasetRlsRoleMember -DatasetId $datasetId -WorkspaceId $workspaceId -Role $user.Role -Member $user.UserPrincipalName
}
Write-Host " RLS users assigned successfully!"
Bulk RLS role assignment completed!
Step 6: Remove a User from an RLS Role
To remove a user from an RLS role:
Remove-PowerBIDatasetRlsRoleMember -DatasetId "<Dataset_ID>" -WorkspaceId "<Workspace_ID>" -Role "SalesManager" -Member "user@yourdomain.com"
User removed from RLS role!
Step 7: Verify Users Assigned to RLS Roles
To list all users assigned to an RLS role:
Get-PowerBIDatasetRlsRoleMember -DatasetId "<Dataset_ID>" -WorkspaceId "<Workspace_ID>" -Role "SalesManager"
Users in RLS role retrieved!
Step 8: Automate RLS Role Assignments
Create a PowerShell script Manage_RLS.ps1:
# Connect to Power BI
Connect-PowerBIServiceAccount
# Import users from CSV
$workspaceId = "<Workspace_ID>"
$datasetId = "<Dataset_ID>"
$users = Import-Csv "C:\Path\To\RLS_Users.csv"
# Assign users to RLS roles
foreach ($user in $users) {
Add-PowerBIDatasetRlsRoleMember -DatasetId $datasetId -WorkspaceId $workspaceId -Role $user.Role -Member $user.UserPrincipalName
}
Write-Host " RLS users assigned successfully!"
Schedule it via Task Scheduler to run automatically.
Automated RLS management enabled!