Site icon R-bloggers

Retrieving user access list to all reports in your PowerBI tenant using Powershell

[This article was first published on R – TomazTsql, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

From previous blogpost(s):

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:

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!

To leave a comment for the author, please follow the link and comment on their blog: R – TomazTsql.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.