Use PowerShell to get all SharePoint Sites for a user
#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.