Destroying Orphaned OneDrive sites; See them Driven before you, and Hear the Lamentation of the Losers

Today’s sampling of client cases… I’ve had two unrelated clients with the same issue: their Microsoft Cloud App Security alerts went bonkers over potential PCI sensitive file content in user OneDrive folders, where the user accounts had been deleted LONG ago. How long ago? 2015 to be precise.

Since the user account was obliterated long ago, nothing shows in AzureAD, or SharePoint, etc. And since the ownership is (or was) still tied to the missing account, the URL link in each MCAS alert wouldn’t open for the tenant admin (Global or SharePoint administrator).

So, first we needed to find the orphaned turds in the cloud kitty litter box. The following script will dump the turds into a CSV bag for auditing and review. Then pepper spray each one so the new admin person can access the turds in the folders and destroy them.

This was adapted from the examples in https://docs.microsoft.com/en-us/onedrive/list-onedrive-urls and https://www.sharepointdiary.com/2015/08/sharepoint-online-add-site-collection-administrator-using-powershell.html

param (
  [parameter()][string] $TenantUrl = "https://contoso-admin.sharepoint.com",
  [parameter()][string] $CsvFile = ".\contoso_onedrive_users.csv",
  [parameter()][string] $AdminUser = ""

try {
  Write-Host "connecting to AzureAD and SharePoint Online"
  Connect-AzureAD | Out-Null
  Connect-SPOService -Url $TenantUrl | Out-Null

  Write-Host "requesting Azure AD users"
  $adusers = Get-AzureADUser -All $True
  Write-Host "requesting SharePoint OneDrive personal sites"
  $odusers = (Get-SPOSite -IncludePersonalSite $True -Limit All -Filter "url -like '-my.sharepoint.com/personal/'")

  Write-Host "comparing site owners with Azure AD users"
  $odata = @()
  $odusers | Foreach-Object {
    if ($_.Owner -notin $adusers.UserPrincipalName) {
      if (![string]::IsNullOrEmpty($AdminUser)) {
        Write-Host "adding site collection admin for: $($_.Url)"
        Set-SPOUser -Site $_.Url -LoginName $AdminUser -IsSiteCollectionAdmin $True
      $odata += [pscustomobject]@{
        Url = $_.Url
        Owner = $_.Owner
  if ($odata.Count -gt 0) {
    $odata | Export-Csv -Path $CsvFile -NoTypeInformation 
    Write-Host "$($odata.Count) sites found. Saved to: $CsvFile"
  } else {
    Write-Host "no orphaned sites found"
catch {
  Write-Error $_.Exception.Message 

There are a hundred quadrazillion to the forty-five thousandth power variations for coding this, and this is just one. And more than likely it’s the one instance you will point a finger at and shake your head, thinking “I could’ve done this better”, and you’re right, you could have done this better. Go ahead and pat yourself on the back, I’ll wait…

Ok, so you may have noticed I used a cheap method for controlling the part of the process where it changes ownership on the sites, by checking for the $AdminUser value being blank (or not). Just run it as-is (well, change the defaults for your environment first) to get the OneDrive sites without a corresponding Azure AD user.

Then run it again with a UPN assigned to the $AdminUser parameter to apply ownership changes. Keep in mind that the $AdminUser must have SharePoint Administrator role membership.

And now I need to go for a walk. I forgot how to use my legs.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s