List all external users on all SharePoint sites with PowerShell

April 6th, 2018 | Posted by Marijn in 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 = "https://mytenant-admin.sharepoint.com"
$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"
  }
}

You can follow any responses to this entry through the RSS 2.0 You can leave a response, or trackback.

Leave a Reply

%d bloggers like this: