Retrieving user access list to all reports in your PowerBI tenant using Powershell
This article is originally published at https://tomaztsql.wordpress.com
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!
Thanks for visiting r-craft.org
This article is originally published at https://tomaztsql.wordpress.com
Please visit source website for post related comments.
