How to get all Users in SharePoint Online Sites using PowerShell

Do you need a list of all SharePoint Online users and groups? If so, PowerShell can assist! PowerShell can be used to export users and groups from a SharePoint Online site. Let’s look at how to quickly create an overview report in Excel format for site users and groups.

To get started using PowerShell to manage SharePoint Online, you need to install the SharePoint Online Management Shell and connect to SharePoint Online.

In case you don’t have installed the module, open a PowerShell as admin and run: Install-Module -Name Microsoft.Online.SharePoint.PowerShell

Connect-SPOService -Url https://YourTenant-admin.sharepoint.com

Verify SharePoint Online Management Shell connectivity: Double-check that you are connected to SharePoint Online Management Shell using the correct URL for your SharePoint environment. You can verify the connection by running the Get-SPOSite command and ensuring it returns the list of sites without any errors.

We have to display user, group and site name in report. Microsoft 365 reports show anonymous usernames instead of the actual usernames in the following reports by default:

Email Activity

Mailbox Activity

OneDrive files

SharePoint Activity

SharePoint Site Usage

Microsoft Teams Activity

Yammer Activity

Active users in Microsoft 365 Services and Apps

Groups Activity

Let’s change this setting:

Go to the Microsoft 365 admin center., next go to Settings > Org Settings > Services, and finally select Reports.

Clear Display concealed user, group, and site names in all reports, and then select Save.

Now run the PS Script:

# Connect to SharePoint Online

Connect-SPOService -Url https://Your Tenant-admin.sharepoint.com

# Get all sites in SharePoint

$sites = Get-SPOSite -Limit All

# Initialize an array to store the access report

$accessReport = @()

# Iterate through each site

foreach ($site in $sites) {

    $siteUrl = $site.Url

    $users = Get-SPOUser -Limit ALL -Site $siteUrl

    # Iterate through each user in the site

    foreach ($user in $users) {

        $userObject = New-Object PSObject

        $userObject | Add-Member -MemberType NoteProperty -Name “SiteUrl” -Value $siteUrl

        $userObject | Add-Member -MemberType NoteProperty -Name “LoginName” -Value $user.LoginName

        $userObject | Add-Member -MemberType NoteProperty -Name “IsSiteAdmin” -Value $user.IsSiteAdmin

        $userObject | Add-Member -MemberType NoteProperty -Name “Groups” -Value ($user.Groups | Out-String)

        $accessReport += $userObject

    }

}

 # Export the access report to a CSV file

$accessReport | Export-Csv -Path “C:\Export\File.csv” -NoTypeInformation

Also, you may find my code on GitHub:

Nikolaidis-CloudStories/SharepointOnline: SharepointOnline (github.com)