Getting the list of Workspace users and their access type into a Power BI report

[Anonymous]

HI community,

I want to get the list of one of our Workspace users and their access types in Power BI service. If there is such a data available I would like to connect a power BI to that data and create a report for it. Can you guide me on how to get access to such data?

Best regards

[petchadj]

This PowerShell script works for me once I activate the Fabric Admin role via PIM. It'll output the user or AD group that is provisioned to each respective workspace excluding personal workspaces:

# Connect to Power BI Service Account
Connect-PowerBIServiceAccount

# Connect to Azure AD
Connect-AzureAD

# Get workspaces
$WorkSpace_Users = Get-PowerBIWorkspace -Scope Organization -Include All -All

# Iterate through users and resolve both user and group names
$WorkSpace_Users | ForEach-Object {
    $Workspace = $_.name
    foreach ($User in $_.Users) {
        # Resolve the name for users and groups
        $UserName = if ($User.PrincipalType -eq "Group") {
            # Resolve group name using AzureAD
            $Group = Get-AzureADGroup -ObjectId $User.Identifier
            $Group.DisplayName
        } elseif ($User.PrincipalType -eq "User") {
            # Use DisplayName, fallback to UserPrincipalName or Identifier if DisplayName is blank
            if (![string]::IsNullOrEmpty($User.DisplayName)) {
                $User.DisplayName
            } elseif (![string]::IsNullOrEmpty($User.UserPrincipalName)) {
                $User.UserPrincipalName
            } else {
                $User.Identifier
            }
        } else {
            # Fallback for other types, just use Identifier
            $User.Identifier
        }

        [PSCustomObject]@{
            WorkspaceName     = $Workspace
            UserName          = $UserName
            PrincipalType     = $User.PrincipalType
            AccessPermission  = $User.AccessRight
        }
    }
} | Select UserName, PrincipalType, AccessPermission, WorkspaceName | Where-Object {$Workspace -NotLike "PersonalWorkspace *"}

[devesh_gupta]

@Anonymous 

Power BI does not have a direct built-in feature to retrieve a list of users and their access types in a workspace from within the Power BI Service. However, you can achieve this by using Power BI PowerShell cmdlets to interact with the Power BI Service programmatically.

You might approach like this:

1. Install and Set Up Power BI PowerShell:
- Install the Power BI Management module by running the following PowerShell command:

Install-Module -Name MicrosoftPowerBIMgmt

2. Connect to Power BI Service:
- Use the `Connect-PowerBIServiceAccount` cmdlet to sign in to your Power BI Service account.

3. Get Workspace Users and Their Access Types:
- Use PowerShell cmdlets or the REST API to retrieve a list of users and their access types for a specific workspace. You can use cmdlets like `Get-PowerBIWorkspace` and `Get-PowerBIWorkspaceUser`.

For example, you might use PowerShell code like this:

$workspaceId = "YourWorkspaceId"
$users = Get-PowerBIWorkspaceUser -WorkspaceId $workspaceId

4. Load Data into Power BI:
- Once you have the data in PowerShell, you can export it to a CSV file or connect Power BI directly to the PowerShell session.

If this helps you, please mark it as an accepted solution to help other users find it more easily.

[Anonymous]

Hi @devesh_gupta ,

Thank you very much for the detailed answer. Just one final question: You mentioned that it is possible to connect the data in PowerShell directly into Power BI... How can I do that?

[devesh_gupta]

@Anonymous You can follow this article for the step-by-step process for loading the data from power shell to power bi: How to Push Data via PowerShell to PowerBI - Petri IT Knowledgebase

If you find this insightful, please provide a Kudo and accept the previous detailed answer as a solution so that other users can find it more easily.

[Anonymous]

@devesh_gupta It does not understand the command of Get - PowerBIWorkspaceUser:

$users = Get-PowerBIWorkspaceUser -WorkspaceId $workspaceId

What am I missing?

[devesh_gupta]

@Anonymous Yeah, you're right actually "MicrosoftPowerBIMgmt" module doesn't have this “Get-PowerBIWorkspaceUser” cmdlet. It has these cmdlets only as mentioned below in the screenshot from official microsoft documentation.

[Anonymous]

OK... Now we need another solution for the topic.