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 = "[email protected]"
$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!