Why SharePoint 2013 users added as individual magically removed and how to fix the issue

Recently SharePoint 2013 users reported that they added users to access SharePoint site, however, the added users are not able to access the site. As matter of fact, they are removed from the site magically!

After debugging this issue, we found the root cause is SharePoint 2013 user profile SID is out of sync with AD SID. We believe SharePoint recognized as invalid users and removed them from the site. Let’s dig this into more details.

First how user’s AD SID changes? We have worked with AD team and identified two sceneries SID could change.

After user left the company and the AD account terminated, if the same user re-join the same company, AD will assign the same login ID and UPN. However, this user will have different SID. The following query will display one user in this case. You will see two entries and the 2^nd is user re- join the company.

Query is here:
Get-ADObject -LDAPFilter "(samaccountname=userId)" -IncludeDeletedObjects -Properties objectsid,whencreated,whenchanged | select name,whencreated,whenchanged,objectsid | fl 

If user change the AD domain like change from “domain1” to “domain2”, AD SID will change but the SID history will track the old SID. Below is the example.

The query is listed here: get-aduser -Identity <userId> -Server domain.mycompany.com -Properties sidhistory

Second let’s identify why SharePoint will remove these users. 

Please find the details in depth to understand what happens internally when you delete a user and recreate it with the same name:

At some point the user had been imported by User Profile Synchronization (Profile Sync), deleted from Active Directory, recreated in Active Directory with the same account name, and then re-imported by Profile Sync.  When the user is re-imported, their SID is not updated in the UserProfile_Full table.  Now the SID in the UPA doesn’t match the SID in the UserInfo table for the site collections.

This causes a chain-reaction as below:

• Import a user using Profile Sync. – They get a record created with proper SID in UserProfile_Full table and UserProfileValue table in the Profile database. The SIDs match in both tables at this point.
• Delete and re-create that user in Active Directory. – They will have the same account name, but a new SID.
• Run another Profile Sync. – The existing profile record will be updated with the new (good) SID in the UserProfileValue table, but the SID stored in UserProfile_Full will not be updated. It will retain the old (bad) SID. We now have a SID mismatch.
• Give the user permission to a site, list, document, etc. -- It will be added to the site permissions with the new (good) SID.
• The user opens a file in Office Web Apps. -- Part of the Office Web Apps authentication process (OAuth) is to call out to the User Profile Service Application (UPA) to get information about the user to augment their claims set and use that to open the file.
• The UPA returns the old (Bad) SID in the Oauth token.
• The Oauth token is presented to the SharePoint site to try to open the document.
• The authorization process finds the user by account name in site permissions – Since the user has the same account name but different SID, the existing user record gets deleted from the site collection, removing all user permissions.
• In SharePoint, the SID is treated as the unique ID for the user. It doesn’t matter what the account name is, if you have a different SID, you are a different user.
• Since we can’t have more than one user with the same account name active at any given time, the original user record is marked as deleted and all of the permissions for that user are removed.  
• This is why the user gets “Access Denied” and must be added back to site permissions.
• When the user is added back to the site, they are added back using their correct (good) SID.  This effectively marks their ‘Bad’ record in the UserInfo table as deleted, and re-activates their ‘good’ record. – The user is fine until they go through the Oauth process again.

Note: The above scenario involves Office Web Apps (OWA), but this same thing could happen with any feature that uses OAuth.  This includes (but is not limited to): Office Web Apps, Workflow, Site Mailboxes, SharePoint-hosted Apps, and Provider-hosted Apps).

Third step is to identify users SID out of sync. There are two ways to get users with mismatch SID. 

The first way is to loop through SharePoint user profile and compare the SID with SID from AD query. Here is snippet of the code.

# Get SID from SharePoint user profile

$snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}

if ($snapin -eq $null) {

  Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

}

$userID="domain\ferrari2"

$ca = Get-spwebapplication -includecentraladministration | where {$_.IsAdministrationWebApplication}

$spsite = $ca.url

$site = Get-SPSite $spsite

$context = Get-SPServiceContext $site

$upsa = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)

$profile = $upsa.GetEnumerator() |Where-Object {$_.AccountName -eq $userID}

$userProfSID = $profile["SID"].Value

$userProfSID 

# Get SID from AD

$adUser = Get-aduser -Server “domain” "ferrari2"

$adUserSID = $adUser.SID.Value

$adUserSID

The second way is to identify the Profiles where the SIDs don’t match between UserProfile_Full and UserProfileValue

select upf.RecordId, upf.NTName, upf.PreferredName,  upv.PropertyVal as [SIDfromUserProfileValue], pl.PropertyName, upv.PropertyID into #temp from UserProfile_Full upf (nolock) join UserProfileValue upv (nolock)on upf.RecordID = upv.RecordID join PropertyList pl (nolock) on pl.PropertyID = upv.PropertyID where upv.propertyid = 2 select upf.RecordId, upf.NTName, upf.PreferredName, upf.SID as [SIDfromUserProfile_Full], #temp.SIDfromUserProfileValue from UserProfile_Full upf (nolock) join #temp on upf.RecordID = #temp.recordid where upf.SID != #temp.SIDfromUserProfileValue drop table #temp

Now the forth step is to fix the issue. 

We need to update the SID in the UserProfile_Full table.  One way to do this would be to delete all the of the problem profiles and re-import them.  However, all of those users would lose profile data that is manually entered (like “About Me”, “Ask me about”, “Skills”, “Interests”, etc).

Instead, you can run Move-SPUser to update the SID in the UserProfile_Full table to be the “Good” SID for the user. Since we’ll be passing the same account name as both the ‘old’ and ‘new’ account, the SID will be the only change for the user.  Here’s an example of running this for one user:

$url = "http://www.contoso.com/" $claimsAcc = "i:0#.w|contoso\user1" $user = Get-SPUser -Identity $claimsAcc -Web $url Move-SPUser -Identity $user -NewAlias $claimsAcc -IgnoreSID

If you have a large number of users in this state, you’ll want to run this in a script that loops through each user.  We can use the previous script to dump users with mismatch SID in to a csv file. The use the following script to fix the SIX issue.

#Synopsis: Use this to run move-spuser against a list of account names stored in a CSV
#The script calls move-spuser to fix the issue.  Move-spuser is a farm-wide operation, so it only needs to be run once per-user.
#The “$URL” variable can really be any site collection in the farm.  The script just requires a single "spweb" object so that it can establish the proper context.
#Just set the top three variables: $url, $path, $logfile

$url = "http://team.contoso.com"  # Any site collection $path = "c:\problemUsers.csv" # The input file with user names to migrate $logfile = "c:\move-SPUserLog.txt" # The output log file Add-PSSnapin microsoft.sharepoint.powershell -ea SilentlyContinue $date = Get-Date -Format U "Started Move-SPUser at " + $date + " (UTC time)" | out-file $logfile -append "===============================================" | out-file $logfile -append $ErrorActionPreference = "stop" $csv = Import-Csv -Path $path [array]$NeedtoFix = @() $web = get-spweb $url foreach($line in $csv) {$NeedtoFix += $line} $fixTotal = $NeedtoFix.Count for($j=0; $j -lt $fixTotal; $j++) { $acc = $NeedtoFix[$j].ntname $claimsAcc = "i:0#.w|"+$acc "Fixing user: " + ($j+1) + " out of " + $fixTotal + " --> " + $claimsAcc | out-file $logfile -Append     try{$user = $web.EnsureUser($claimsAcc)         Move-SPUser -Identity $user -NewAlias $user.UserLogin -IgnoreSID -confirm:$false         write-host "Fixed user: " ($j+1) " out of " $fixTotal " --> " $claimsAcc         }    catch [system.Exception]         {"ERROR!!! for user: " + $claimsAcc + " -- " + $_.Exception.Message | out-file $logfile -append} }

You might need to schedule this fix frequently so users will not have such issues.

Thanks Prerna Vashistha from Microsoft for the detailed explanation and the workaround.