From previous blogpost(s):
- Deleting Power BI datasets using Powershell
- Longterm-storage of Power BI activity logs and statistics using Powershell
- Retrieving list of users for all workspaces in your PowerBI tenant using Powershell
Ever wanted to get the list of all users and their access to reports? The snippet below will return you just that. The list of:
- User Name
- Report Name
- Workspace Name
- Access level
This way, you will have a better view of users, and their access to data and reports (if these are containing sensible data). You can always retrieve the list of workspaces and access the list of all users with the PowerShell cmdlet Get-PowerBIWorkspace.
I have also added the Join-Object module. It can join two objects or two arrays, based on the given matching columns.
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
Import-Module Join-Object
# 1. Login to app.power.bi
$user = "YourAzure.Email@domain.com"
$pass = "YourStrongP422w!!rd"
$SecPasswd = ConvertTo-SecureString $pass -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential($user,$SecPasswd)
Connect-PowerBIServiceAccount -Credential $myCred
# 2. Get list of users and workspaces
$WorkSpace_Users = Get-PowerBIWorkspace -Scope Organization -Include All -All
# 3. Iterate through the workspace and get reports in each workspace (exclude Personal Workspaces)
$reposts_WS = $WorkSpace_Users | ForEach-Object {
$Workspace = $_.name
foreach ($Rep in $_.Reports) {
[PSCustomObject]@{
WorkspaceName = $Workspace
ReportID = $Rep.id
ReportName =$Rep.Name}
}
} | Select ReportID, ReportName, WorkspaceName | Where-Object {$Workspace -NotLike "PersonalWorkspace *"}
# 4. Iterate through the workspace and get users with access policy on each workspace (exclude Personal Workspaces)
$users_WS = $WorkSpace_Users | ForEach-Object {
$Workspace = $_.name
foreach ($User in $_.Users) {
[PSCustomObject]@{
WorkspaceName = $Workspace
AccessPermission = $User.accessright
UserName =$user.Identifier}
}
} | Select UserName, AccessPermission, WorkspaceName | Where-Object {$Workspace -NotLike "PersonalWorkspace *"}
# 5. Merge two data
$joinedWS = Join-Object -Left $reposts_WS -Right $users_WS -LeftJoinProperty 'WorkspaceName' -RightJoinProperty 'WorkspaceName' -Type OnlyIfInBoth -LeftProperties ReportName, WorkspaceName -RightProperties UserName, AccessPermission
Same as in the previous example, I have excluded the Personal Workspaces.
Follow for more Powershell Scripts for Power BI on Github.
Happy scripting and stay healthy!
[…] Tomaz Kastrun wants to know what you can see: […]
LikeLike
join-object module was not found. How to fix it.
LikeLike
You have to install it first.
Install-Module -Name Join-Object
LikeLike
accespermission is empty. How to fix this?
LikeLike
I got the list of users per workspace, but I would like to get the list of users of each report. How would I get that done?
LikeLike
You can retrieve all uaers per report, find report and workspace connection anf ypu shluld be able to get users in workspace.
Best
LikeLike