List all external users on all SharePoint sites with PowerShell


I was having a conversation with a client this morning who was concerned around GDPR, which is ofcourse a hot topic now. This client is doing projects together with external consultants and they wanted to get an overview which externals had access to what sites. I couldn’t immediatly find something like that online, so I decided to build my own script in PowerShell.

As per usual with my code, a little disclaimer: I am not a developer, there are multiple ways to make this script better/faster but I like it this way because of readability and simplicity. Feel free to bring additions in the comments or let me know how you make this happen. Sharing is caring after all!!

How does it work

So, what does this PowerShell script do? Well, it iterates across all site collections (yes, also modern sites). For every site collection, it will iterate through all external people (so called guest accounts) and list them in a csv file with 3 columns: site url, email address and display name. The beauty of working this way is that you can open it in Excel, make it a table and search/filter on either one of the columns to get relevant data back. So if you want to know which sites an external person can access, just filter on the name. If you want to know all external people that can access a specific site, just filter on the url.

I was amazed how quick the script runs, for 800 site collections it took about 10 minutes to list all 2200 instances.

Pitfalls of the script

  • The script only shows the first 50 external people per site collection. If you have more then that you need to modify the script.
  • You need to be site collection admin of the site.
  • The script makes use of PNP PowerShell. If you don’t want to use that, use another way of listing the site collections
  • Script is tested with SharePoint Online sites. Not with any other versions.

 The script

Don’t forget to change the global settings, point the AdminUrl to your admin url. Use the PowerShell tool of your choice to run it.

# Global settings
$AdminURL = ""
$outputfile = "..\AllExternalUsers.csv"

#Connecting to the tenant
Connect-SPOService -url $AdminURL

#Adding column titles to our CSV file
Add-Content -Path $outputfile -Value "Site url;Email address;Display name"

#Looping through all sites
$Sites = Get-SPOSite -Limit ALL
Foreach ($Site in $Sites)
  #show in console panel the sites we are listing
  Write-Host $Site.url
  $CurrentSite = $Site.Url

  #Connect to the site
  Connect-PnPOnline -Url $site.url
  #looping through all external users
  $Externalusers = Get-SPOExternalUser -SiteUrl $site.url -PageSize 50 -Position 0
  Foreach ($Externaluser in $Externalusers)
    #in this case, we are listing the email address and the displayname of our external users
    $UserEmail = $Externaluser.Email
    $UserDisplayName = $Externaluser.DisplayName
    Add-Content -Path $outputfile -value "$CurrentSite ; $UserEmail ; $UserDisplayName"

About: Marijn

Marijn Somers (MVP) has over 14 years experience in the SharePoint world, starting out with SP2007. Over the years the focus has grown to Office 365, with a focus on collaboration and document management. He is a business consultant at Balestra and Principal Content Provider for "Mijn 365 Coach" that offers dutch employee video training. His main work tracks are around user adoption, training and coaching and governance. He is also not afraid to dig deeper in the technicalities with PowerShell, adaptive cards or custom formatting in lists and libraries. You can listen to him on the biweekly "Office 365 Distilled" podcast.

3 thoughts on “List all external users on all SharePoint sites with PowerShell”

Leave a Reply