Script to Clean up SharePoint User Profiles

Greg BurnsGreg Burns (poortatey)
Published:
Updated:
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
On one of my customers SharePoint 2013 farms, I implemented a cool solution, which leverages the User Profile Service and People Search to create a simple-yet-powerful directory of all the employees in the organization. I based it on Ari Bakker's "People Directory" blog post, which you really should give a read, because it works exactly as advertised.

Still, it's garbage in, garbage out, so if your Active Directory is a mess, the People Directory will be a mess, too. So I worked with the customer to target specific OU's for their User Profile Sync, filter out disabled users, move non-human accounts (such as SQL Service and Farm Service) out of the OU, until the OU contained a much better representation of the company's staff.
 
But the old profiles weren't being deleted, so you still had service accounts and domain admins listed alongside legitimate employees. You could, of course, go into the User Profile Service App and remove each invalid profile one by one, but the customer wasn't excited about doing that for the 100+ profiles we estimated had to be pruned. We needed to find a way to prune out the invalid profiles quickly and accurately, so I found a PowerShell script and customized it to do the following:
  • Connect to AD and enumerate all users under the OU "OU=CONTOSO CORP Users,DC=Contoso,DC=local"
  • Connect to the User Profile Service App and enumerate all user profiles

  • For each profile,
    • If there is not a corresponding AD user in "OU=CONTOSO CORP Users,DC=Contoso,DC=local", delete the profile from the User Profile database
    • Next

Note: I've commented out the actual "delete" command below, so you can run it in report mode before you try it for real. Run this script on a SharePoint Farm server; the Active Directory PowerShell module (included with RSAT) will also need to be installed. You need to be logged in with elevated permissions; I'd suggest Farm Admin + Local Admin with SPShellAdmin rights.

DISCLAIMER:  Use extreme caution when running this script (or anything else you download from the internet). Try it on a test machine first so you understand how it works. Make a backup of your production farm. You can potentially lose data / profile settings.

Script: Delete-SpecifiedUserProfiles.PS1


#Script: Delete-SpecifiedUserProfiles.PS1 
                      
                      #Add SharePoint PowerShell SnapIn if not already added 
                      
                      if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {Add-PSSnapin "Microsoft.SharePoint.PowerShell"} 
                      
                       
                      
                      #Enumerate all users under the CONTOSO Corporate Users OU 
                      
                      #Note: This requires the AD PowerShell Module from RSAT to be installed 
                      
                      $CorpUsers = Get-ADUser -SearchBase "OU=CONTOSO CORP Users, DC=Contoso,DC=local" -filter * 
                      
                       
                      
                      #Uncomment to get a report of AD users in your OU 
                      
                      #$CorpUsers | Export-Csv E:\Scripts\CONTOSO_CorpUsers_OU_011215.csv 
                      
                       
                      
                      #Get UserProfileManager from the My Site Host Site context 
                      
                      $site = new-object Microsoft.SharePoint.SPSite("http://YourCentralAdmin:PortNumber/"); 
                      
                      $ServiceContext = [Microsoft.SharePoint.SPServiceContext]::GetContext($site); 
                      
                      $ProfileManager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServiceContext) 
                      
                      $AllProfiles = $ProfileManager.GetEnumerator() 
                      
                       
                      
                      #Uncomment to get a report of Profiles in your User Profile DB 
                      
                      #$AllProfiles2 = $ProfileManager.GetEnumerator() 
                      
                      #$AllProfiles2 | Export-Csv E:\Scripts\SPProd_Profiles_011215.csv 
                      
                       
                      
                      #Loop through each profile in SharePoint User Profile Database 
                      
                      foreach($profile in $AllProfiles) 
                      
                       { 
                      
                       $DisplayName = $profile.DisplayName 
                      
                       $AccountName = $profile[[Microsoft.Office.Server.UserProfiles.PropertyConstants]::AccountName].Value 
                      
                       #Strip the domain so we can compare SAM account names 
                      
                       # Trim the first 8 characters from account name (remove "CONTOSO\") 
                      
                       $UserProfileSAMName = $accountname.substring(8) 
                      
                       write-host $Displayname "(AccountName: " $AccountName ", SAM Name: " $UserProfileSAMName ")" 
                      
                       
                      
                       #If a user profile exists but does not reside in the CONTOSO Corporate Users OU, then it must be removed. 
                      
                       If (-not ($CorpUsers.SamAccountName.contains($UserProfileSAMName))) 
                      
                       {write-host "---> " $UserProfileSAMName " (" $AccountName ") does not exist under CONTOSO Corp Users OU." 
                      
                       #Do not delete setup (admin) account from user profiles. Please enter the account name below 
                      
                       if($AccountName -ne "Contoso\sp_installer") 
                      
                       { 
                      
                       #For this example, the actual command to delete the profile has been commented out. We are reporting only. 
                      
                       # Uncomment if you want to actually delete profiles 
                      
                       # $ProfileManager.RemoveUserProfile($AccountName); 
                      
                       #write-host "---> Profile for account " $Displayname "(AccountName: " $AccountName ", SAM Name: " $UserProfileSAMName ") has been deleted" 
                      
                       write-host "---> Profile for account " $Displayname "(AccountName: " $AccountName ", SAM Name: " $UserProfileSAMName ") would be deleted" 
                      
                       } 
                      
                       } 
                      
                       } 
                      
                      write-host "Finished." 
                      
                      $site.Dispose() 

Open in new window

Download the source code here.

What is the impact of deleting a user profile?


First a little context. In the free edition of SharePoint (SharePoint Foundation), there is no User Profile Service App. So this means that the first time a new user logs in, a local profile is created and stored in the site collection, along with certain of the user's properties pulled from Active Directory. If you click on a user's name (e.g. in the "Created by" field next to a document) you will be taken to their site collection profile page. That's how it works in Foundation. In Standard and Enterprise, it works this way too, unless you create a User Profile Service App. One a UPSA is associated with the farm, clicking a user's name will take you to their UPSA profile, typically located in the webapp that is hosting MySite / Newsfeed / OneDrive.
 

If you delete a UPSA profile, the identity link for that user reverts back to using their site collection profile. This is a good thing. It means you won't lose the metadata associated with a document or item if it happened to be created by that user.
 

So let's say you deleted a profile by accident.


Let's say this is your scenario:

  • User Karen Smith AD user account (CONTOSO\ksmith) is located in CONTOSO CORP Users OU.
  • User Profile AD sync has generated a UPSA profile for her and is synching properties
  • She has logged in to https://portal.contoso.com
  • She clicked on her OneDrive for Business link, taking her to https://my.contoso.com (this also kicked off a timer job to create a MySite site collection for the user, to host her OneDrive files)
  • She has authored documents.
  • She has put documents in her OneDrive for Business site.
  • She has gone to her "About Me" link and edited her profile, updating her Profile picture.
Now let's say you move CONTOSO\ksmith out of that OU and manually delete her UPSA profile. You do not delete her MySite site collection. Then you say, "oops", and want to put it back. You do this:
  • Move CONTOSO\ksmith back into the OU
  • Kick off a sync of the User Profile Connection
 
The next time Karen logs in, she won't see anything different until she clicks on one of the three "mysite" links beside her name. There she'll see the same welcome message she saw the first time, because she's being treated as a new profile. The MySite creation timer job kicks off, but instead of creating a new site collection, she is relinked with her old site collection, so OneDrive shows the same documents she had in there before.
 
However, any customization she did to her profile (i.e. the profile pic) is now erased, and must be done again.
That's as far as my testing of this scenario went, so other than possible user confusion, I did not see any other adverse symptoms. It won't affect any actual AD objects or security, but it will remove profiles. MySites will not be deleted.
Still, it's probably best to avoid deleting user profiles if you don't have to, which is why I was very careful running this script in production (see Disclaimer above).

I originally posted this article to the Alaska SharePoint User Group blog, here: http://www.akspug.org/Blog/Post/96/Script-to-Clean-up-User-Profiles
1
964 Views

Comments (3)

Walter CurtisSharePoint AED
CERTIFIED EXPERT
Distinguished Expert 2023

Commented:
Great information. Thanks! Recommended reading for all SharePoint people. This should be submitted as an article.

Have a good one...
Albert WidjajaIT Professional
CERTIFIED EXPERT

Commented:
Is this will still be working for SharePoint Online ?
Walter CurtisSharePoint AED
CERTIFIED EXPERT
Distinguished Expert 2023

Commented:
This is for SharePoint On-premise.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.