Tim D'Annecy

#SharePoint #PowerShell #ShareGate #M365

Today, I received a request to generate a report of all SharePoint Sites that a user has access to. Currently, the SharePoint admin center portal only has the ability to see which users are members of a group. There's no ability for the inverse: Which Sites does a user have access to?

To do this, I wrote a quick PowerShell script to iterate through all sites, checks if the user is a Member, then outputs the list of sites to a .csv.

Before starting, there are a few requirements to run this script correctly:

• You'll need the Microsoft.Online.SharePoint.PowerShell module. If you don't have it installed, run the PowerShell command as Administrator: Install-Module -name 'Microsoft.Online.SharePoint.PowerShell'

• You'll also need to be a Site Collection Administrator on all sites to get the membership. To do this, you can either run a PowerShell command in the Microsoft SharePoint module or use ShareGate to make the change in the GUI [A] or using the ShareGate PowerShell module [A].

Here's the script:

## Get-UserSPOSiteRole.ps1
## Gets all Sharepoint Site membership for a specified user, then outputs to csv.
## Requires the Site Collection Administrator role on all sites. (Recommended with Powershell or ShareGate)
## tdannecy@gmail.com

$outputfile = 'C:\output.csv'

Import-Module -name Microsoft.Online.SharePoint.PowerShell
Connect-SPOService -Url 'https://example-admin.sharepoint.com'

$userEmail = 'user@example.com'
$output = @()

$sites = Get-SPOSite -Limit All

foreach ($site in $sites) {
    try {
        $permissions = Get-SPOUser -Site $site.Url -LoginName $userEmail -ErrorAction SilentlyContinue
        if ($permissions) {
            $curpermission = [PSCustomObject]@{
                SiteURL  = $site.Url
                UserType = $permissions.UserType
            }
            $output += $curpermission
        }
    }
    catch {
        if ($_.Exception.Message -like '*User cannot be found*') {
            Write-Host "No permissions on $($site.Url)"
        }
        else {
            Write-Error "Error on $($site.Url): $($_.Exception.Message)"
        }
    }
}
$output | Export-Csv -Path $outputfile -NoTypeInformation
Invoke-Item -Path $outputfile

I also saved this script as a GitHub Gist:

I hope this helps!

Update: I also have a similar script to get all Shared Mailboxes for a user in a separate post.

Discuss...