This is part 4 in a series. You can find other parts here:

SharePoint: The complete guide to user profile cleanup – Part1

SharePoint: The complete guide to user profile cleanup – Part 2 – 2010

SharePoint: The complete guide to user profile cleanup – Part 3 – 2013

SharePoint: The complete guide to user profile cleanup – Part 5 – 2019

Update 2/25/20: Found that purgeNonimportedObjects works differently in SharePoint 2016. See the ** amendment in the “ADI Step 3” section below.

Update 12/8/19: Added that deletions are processed differently than other ‘out of scope’ scenarios. See the * amendment in the “ADI Step 2” section below.

Sync Options:

In SharePoint 2016, you have two options. Like SharePoint 2013, you can use Active Directory Import (aka: “AD Import”, “ADI”). You also have the option of using an “External Identity Manager”. In most cases, this will be Microsoft Identity Manager 2016 (aka: MIM), which is the successor to Forefront Identity Manager (FIM).

Active Directory Import (aka: ADI)

ADI Step 1: Determine if the profile is already marked for deletion.

Run this SQL query against the Profile database:

Select * From upa.UserProfile_Full Where bDeleted = 1

If your target profiles are in the results, that means they are already marked for deletion. All you should need to do is run the My Site Cleanup Job. See step 4 below.

Note: Managed profiles marked for deletion should also show in Central Admin | Your UPA | Manage User Profiles | Profiles Missing from Import.

ADI Step 2: Run a Full Import.

“Out of Scope” (disabled, filtered out, moved to a non-imported OU) users do not have their profiles automatically cleaned up by an incremental import*.  With AD Import, we don’t use the Sync database to store “state” information about each user.  As such, the only way AD Import can tell if a user has fallen “out of scope” is to import them.  If the user object has not changed in AD, an incremental import will not pick them up.  Luckily, AD Import is fast, so running a Full Import is not a big deal.  For more on this, see my colleagues post on the subject: https://blogs.msdn.microsoft.com/spses/2014/04/13/sharepoint-2013-adimport-is-not-cleaning-up-user-profiles-in-sharepoint-whose-ad-accounts-are-disabled/

Note: By default, SharePoint only runs Incremental imports on a schedule. In order to run a Full import on a recurring schedule, you’ll have to create a Windows Scheduled Task and kick it off with PowerShell like this:

$UPA = Get-SPServiceApplication | ? { $_.typename -match "profile"}
$UPA.StartImport($true) 

* The exception to this rule is if you directly delete AD users from a synched OU (one that is selected for import). In that case, the deletion of the profile is processed automatically during the next import. You’d see entries like this in the ULS log during the import (if your logging is Verbose):

11/19/2019 14:05:47.48 OWSTIMER.EXE (0x1F34) 0x1098 SharePoint Portal Server User Profiles aei5q Verbose QueueItemChange: Incoming delete for item <GUID=c5b7cfb9-d98d-437a-acc5-da1bc6453a4c>;<SID=S-1-5-21-1700552430-3460358242-3531541990-1701>;CN=User1\0ADEL:c5b7cfb9-d98d-437a-acc5-da1bc6453a4c,CN=Deleted Objects,DC=contoso,DC=com of type 0. 2377199f-f0a8-e088-a10e-e2e15e2daf5a

11/19/2019 14:05:47.51 OWSTIMER.EXE (0x1F34) 0x1098 SharePoint Portal Server User Profiles c8hz Verbose ProfileImportExportService.UpdateWithProfileChangeData: Begin Delete CN=User1\0ADEL:c5b7cfb9-d98d-437a-acc5-da1bc6453a4c,CN=Deleted Objects,DC=contoso,DC=com 9c570013-54d5-4ab2-8097-8f90d6985a0b

11/19/2019 14:05:47.54 OWSTIMER.EXE (0x1F34) 0x1098 SharePoint Portal Server User Profiles c8i0 Verbose ProfileImportExportService.UpdateWithProfileChangeData: End Delete CN=User1\0ADEL:c5b7cfb9-d98d-437a-acc5-da1bc6453a4c,CN=Deleted Objects,DC=contoso,DC=com 9c570013-54d5-4ab2-8097-8f90d6985a0b

ADI Step 3: Mark non-imported profiles for deletion.

Run the following PowerShell to get a list of all your unmanaged profiles:

$upa = Get-spserviceapplication | ?{$_.typename -match "profile"}
Set-SPProfileServiceApplication $upa -GetNonImportedObjects $true | out-file c:\temp\NonImportedProfiles.txt

If the target profiles show up in the “NonImportedProfiles.txt” file, then you need to manually mark them for deletion with PowerShell by calling PurgeNonImportedObjects:

$upa = Get-spserviceapplication | ?{$_.typename -match "profile"}
Set-SPProfileServiceApplication $upa -PurgeNonImportedObjects $true

** When you run the “Purge”, it calls a one-time timer job called “Service Application Instance Provisioning Job”. Among other things. that job flips a flag called “purgeNonimportedObjects” to a value of 1 on the UPA object.

The above PowerShell itself doesn’t update any profiles. All it does is flips that flag. You must run another import (Incremental will suffice) to update the users. When the next import runs, it will check the “purgeNonimportedObjects” flag. Since it’s now set to 1, it calls stored procedure “ImportExport_PurgeNonimportedObjects_fltUserProfileUPNRenaming” which does the following:

• Flips the bdeleted flag in the upa.UserProfile_Full table to 1 for each purged user profile.
• Renames the purged profiles to the -DELETED-<guid> format. For example: CONTOSO\Josh-DELETED-0ADC957E-088D-4405-B96A-FA6ABAD1BC42

After the import, the “purgeNonimportedObjects” flag is set back to 0.

So considering the above, if the “Purge” doesn’t seem to be doing what you expect, you may either have a Timer service problem (see my post on that), or it may be that you just haven’t run another import yet.

If the target profiles are managed profiles, that are not marked for deletion, and you have run a Full Import and a Purge, and then another import, then you need to look into why AD Import is not marking them for deletion. Usually it’s an LDAP filter or OU selection issue.

Document your connection filter and selected OUs / containers and check your target profiles against them. If you’re using a complex LDAP filter on your import connection, you should consider using an LDAP tool like LDP.exe or LDAP Browser to test the LDAP filter and make sure it includes and excludes the users you think it should.

ADI Step 4: My Site Cleanup Job

While PurgeNonImportedObjects plus another import will mark out-of-scope profiles for deletion, it doesn’t actually delete anything. That’s left to the My Site Cleanup Job.

Check Central Administration | Monitoring | Timer Jobs | Review Job Definitions | My Site Cleanup Job. Make sure it’s set to run at least once per day.

Important: In SharePoint 2016, there were some major changes made to how the My Site Cleanup Job works. Instead of immediately deleting profiles that are marked for deletion, it schedules the profiles to be deleted after 30 days. The 30-day setting is hard-coded. There is no way to change it. Also, if your build is pre-August 2017 CU (16.0.4573.1002), this functionality does not work at all, even after 30 days. You’ll need to upgrade. See this post for details: https://blogs.msdn.microsoft.com/spses/2017/05/22/sharepoint-2016-mysitecleanup-job-functionality-changes/

If for some reason you can’t wait 30 days to get rid of these profiles, then you’ll have to delete them via PowerShell script. My colleague Adam has a nice option for doing that here: https://adamsorenson.com/deleting-user-profiles-using-powershell/

I’ve also added my own take on a profile deletion script, which is slightly more automated as you don’t have to prepare the input file. Instead, it just deletes all profiles that are bDeleted = 1 in the upa.userprofile_Full table of the Profile database. Please keep in mind that the script is meant for a situation where a bunch of new unwanted profiles got imported. Because it only deletes the users profile, and will not delete their MySite, it’s not meant to remove profiles for established users that also have My Sites.

########################### SCRIPT ###########################
# RemoveBDeletedProfiles.ps1
# Author: Joroar, et al.
# This PowerShell script is provided "as-is" with no warranties expressed or implied.  Use at your own risk.
# Please back up your UPA databases before running this.
# This script will access the UPA associated to the web application given and delete all the user profiles that are marked for deletion 
# It will delete all the user profiles that have the BDeleted flag set to 1
# It also makes a web request to log usage data about how often this script is used
# Only one value that needs to be updated below, the $webapp variable.  You can also adjust the log location in $logPath if you like
# If there is more than one UPA in the farm, it will prompt you to choose the correct one.
# If you'd like to test a "dry run" first without removing any profiles, just comment out the "$pm.RemoveUserProfile($id)" line

# Update the web application with one that is associated with the target UPA
$webapp = "http://teams.contoso.com"
$logPath = "c:\temp\"
# Declaring and creating the log files. Each time the script is executed, a new file will be created with the current time in the filename.
$dateTime =Get-Date -format "dd-MMM-yyyy HH-mm-ss"
$UPLogFile = $logPath + "UserProfiles_Remove_bdeleted" +"_"+ $dateTime + ".log"
asnp *sharepoint* 
# Determine SharePoint version so we run the right SQL query:
$build = get-spfarm | select buildversion
if($build.BuildVersion.major -ge 16)
{$is2016 = $true}
else {$is2016 = $false}
# SQL Query function.
function Run-SQLQuery ($ConnectionString, $SqlQuery)
{$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
} 
# Declaring the SQL connection String and running the SQL query to gather profiles marked bDeleted.
$upa = Get-SPServiceApplication | where {$_.TypeName -eq "User Profile Service Application"}
if ($upa.Count -gt 1)
{Write-Host "More than 1 UPA Detected"
$upa | select name, id
$UPAID = Read-Host "More than 1 UPA in the farm.  Please enter the ID for the UPA to run this against" 
$upa = Get-SPServiceApplication $UPAID
}
$propData = $upa.GetType().GetProperties([System.Reflection.BindingFlags]::Instance -bor [System.Reflection.BindingFlags]::NonPublic)
$profDatabase = ($propData | where {$_.Name -eq "ProfileDatabase"})
$prof = $profDatabase.GetValue($upa) 
$connStr = $prof.DatabaseConnectionString
If ($is2016)
{$inputFile = Run-SQLQuery -ConnectionString $connStr -SqlQuery "SELECT [NTName] FROM upa.UserProfile_Full where bDeleted = 1"}
Else {$inputFile = Run-SQLQuery -ConnectionString $connStr -SqlQuery "SELECT [NTName] FROM UserProfile_Full where bDeleted = 1"}
If ($inputFile -eq 0)
{Write-Host "No profiles marked for deletion in the specified UPA:" 
$upa | select name, id
}
else{
# Declaring the Sharepoint Variables.
$site = new-object Microsoft.SharePoint.SPSite($webapp);
$ServiceContext = [Microsoft.SharePoint.SPServiceContext]::GetContext($site);
$pm = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServiceContext) 
$inputFile | Foreach-Object($_){
$User=$_.ntname
if($User -ne $null)
{Write-Host "User Name: $User"
try
{$profile = $pm.GetUserProfile($User)
$DisplayName = $profile.DisplayName
Write-host “Current User:” $DisplayName
$messageDisplayname = “Current User: ” + $DisplayName
Add-Content -Path $UPLogFile -Value $messageDisplayname
$AccountName = $profile[[Microsoft.Office.Server.UserProfiles.PropertyConstants]::AccountName].Value
$id=$profile.ID
Write-host “ID for the user : ” $DisplayName “ is ” $id
$messageid = “ID for the user : ” + $DisplayName + “ is ” + $id
Add-Content -Path $UPLogFile -Value $messageid
try
{
$pm.RemoveUserProfile($id)
Write-host “Successfully Removed the Profile ” $AccountName
$messagesuccess = “Successfully Removed the Profile ” + $AccountName
Add-Content -Path $UPLogFile -Value $messagesuccess
Add-Content -Path $UPLogFile -Value ” ”
}
catch
{Write-host “Failed to remove the profile ” $AccountName
$messagefail = “Failed to remove the profile ” + $AccountName
Add-Content -Path $UPLogFile -Value $messagefail
Add-Content -Path $UPLogFile -Value ” ”
}}
catch
{Write-host “Exception when handling the user $User - $($Error[0].ToString())"
$messageexcp = “Exception when handling the user ” + $User
Add-Content -Path $UPLogFile -Value $messageexcp
Add-Content -Path $UPLogFile -Value ” ”
}}}}
########################### SCRIPT ###########################

External Identity Manager (aka: “MIM Sync”)

MIM Sync Step 1: Determine if the profile is already marked for deletion.

Run this SQL query against the Profile database:

Select * From upa.UserProfile_Full Where bDeleted = 1

If your target profiles are in the results, that means they are already marked for deletion. All you should need to do is run the My Site Cleanup Job. See step 4 below.

Note: Managed profiles marked for deletion should also show in Central Admin | Your UPA | Manage User Profiles | Profiles Missing from Import.

MIM Sync Step 2: Determine if the profile is managed or unmanaged.

Run the following PowerShell to get a list of all your unmanaged profiles:

$upa = Get-spserviceapplication | ?{$_.typename -match "profile"}
Set-SPProfileServiceApplication $upa -GetNonImportedObjects $true | out-file c:\temp\NonImportedProfiles.txt

If the target profiles show up in the “NonImportedProfiles.txt” file, then you need to manually mark them for deletion with PowerShell:

$upa = Get-spserviceapplication | ?{$_.typename -match "profile"}
Set-SPProfileServiceApplication $upa -PurgeNonImportedObjects $true

If the target profiles are managed profiles and not marked for deletion, then you need to look into why the Sync is not marking them for deletion.

Document your Sync connection filters and selected OUs / containers and check your target profiles against them.

Take a look at the MIM Client (miiscleint.exe) on your MIM server. Detailing exactly what to look for in the MIM client is beyond the scope of this blog post, but generally speaking, if you have entire Sync steps that are failing, that’s likely the problem.

MIM Sync Step 3: Run a Full Sync.

If you’ve made recent changes to your Sync connection filters or AD container selection, it takes a Full Sync to apply those changes to all profiles. Also, an Incremental Sync only gets one shot at updating a profile. If something went wrong during the Incremental that ran right after the user fell out-of-scope (deleted from AD, etc), that change is missed. If the user object in AD does not change again, the Incremental will not attempt to pull that user in again. Therefore, a failure during a single run of the Sync could cause the profile to never be processed. For this reason, we recommend that you run a Full Sync on some type of recurring schedule. The interval is up to you, but something between once a week and once a month should work.

MIM Sync Step 4: My Site Cleanup Job

This step is exactly the same as the “ADI Step 4: My Site Cleanup Job” section above. See that.