Report on all sites a user is an owner or member of

[BarryAnderson]

I need to create an output which lists all the sites a named user is the owner or a member of. I thought this would be fairly simple in PowerShell. I could simply iterate through all sites, read the memberships and filter. However, am running into difficulty with the Get-SPOUser command as it tells me my Global Admin account does not have permission to list the site members.

Error for Get-SPOUser (running as Global Admin):

Get-SPOUser : Access denied. You do not have permission to perform this action or access this resource.

Script I started:

# Connect to SharePoint Online Admin
Write-host "Connecting to Admin Center..." -f Yellow
Connect-SPOService -Url $Admin_Url

# Get all site collections
Write-host "Getting All Site collections..." -f Yellow
$Sites = Get-SPOSite -Limit 10 # -Limit ALL

ForEach($Site in $Sites) {
    Write-host "Getting Users from Site collection:"$Site.Url -f Yellow
    Get-SPOUser -Limit ALL -Site $Site.Url | Select DisplayName, LoginName
}

Is there a simpler or already baked way to achieve my request?

If not how do I query site membership using Global Admin account?

[#2 · Accepted solution]

Hey Barry,
Unfortunately, you must be a site collection administrator (SCA) of a site to be able to have that script work successfully.
Being a Global Administrator does not grant you access to all SharePoint sites within your tenant.
The Global Admin role allows you to add your account as a site collection administrator to all the sites in your tenant, enabling you to run this script and other scripts.

Your script worked beautifully for me BTW as I am a SCA for all sites in my tenant. Thanks for sharing it. 

If you need to add yourself as a secondary SCA to all sites, you can use: 

Connect-PnPOnline -Url $SiteURL.Url -Interactive

Set-SPOUser -Site $SiteURL.Url -LoginName $UserName -IsSiteCollectionAdmin $true

Notes: 

• It will add the user as a secondary site collection administrator and not overwrite existing administrators including the primary administrator.
• It will work if you are not an Admin for the site but have the SharePoint admin role.

[#3]

Also want to add that Add-PnPSiteCollectionAdmin doesn't work because you must be a Site Collection Admin to run this command. It does not replace or remove existing site collection administrators.