SharePoint: Users randomly lose permission – are deleted from site

This is a good one, it appears to be both random and intermittent, and is extremely hard to track down. It’s known as the “SID mismatch” problem.

Consider the following scenario:

Intermittently, when a user browses to a resource (site, list, etc) that they are supposed to have access to, they receive “Access Denied“, or our friendlier version: “Sorry, this site hasn’t been shared with you“.

When looking at permissions, you find that the user no longer has any explicitly-given permission within the site collection.
The permission to the resource has been removed and the user must be added back. In fact, the user has been deleted from the entire site collection. The only permissions they may have left are those they get via Active Directory (AD) group membership.

Cause:

At some point, the user had been imported by User Profile Synchronization (Profile Sync), deleted from Active Directory, re-created in Active Directory with the same account name, and then re-imported by Profile Sync.  When a user is re-created in AD in this way, they have the same account name, but they have a new security identifier (SID) value. When the user is re-imported to SharePoint, their SID is not updated in the UserProfile_Full table.  Now the SID in the User Profile Service Application (UPA) doesn’t match the SID in the UserInfo table used by the site collections.

Verify you’re hitting this issue:

You can run the following SQL query against the Profile database for the User Profile Service Application to identify users that are in this state.

SharePoint 2016+ query:

-- Identify the Profiles where the SIDs don’t match between UserProfile_Full and UserProfileValue:
-- SharePoint 2016 and above version
select upf.RecordId, upf.NTName, upf.PreferredName,  upv.PropertyVal as [SIDfromUserProfileValue], pl.PropertyName, upv.PropertyID
into #temp
from upa.UserProfile_Full upf (nolock) 
join upa.UserProfileValue upv (nolock)on upf.RecordID = upv.RecordID 
join upa.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 upa.UserProfile_Full upf (nolock)
join #temp on upf.RecordID = #temp.recordid
where upf.SID != #temp.SIDfromUserProfileValue
drop table #temp

SharePoint 2013 query:

-- Identify the Profiles where the SIDs don’t match between UserProfile_Full and UserProfileValue:
-- SharePoint 2013 Version
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

Users listed in the query results will match the users having this random loss of permission, and may include some that you don’t know about.
Note: The SIDs listed in the results are encoded.  You’d have to use some PowerShell to decode them to the familiar “S-1-…” format, but that is unnecessary for our purposes. The main point is to identify that they are different.

Another clue is that these problem users will have multiple records in the UserInfo table in the content database, each with a different value for tp_systemID (which is their encoded SID).  One of them will be marked as deleted (tp_deleted > 0)
You can verify that by running the following SQL query against the content database:

select * from userinfo (nolock) 
where tp_login like '%YourUsersNameHere%' 
and tp_siteid = 'YourSiteCollectionIDHere'

Example:

select * from userinfo (nolock) 
where tp_login like '%josh%' 
and tp_siteid = '020E1B20-92B7-4CBC-B072-EA9369204350'

Not sure of your site collection ID?  Run this PowerShell to get it:

(get-spsite http://YourSiteURLHere.contoso.com).id

Note: Multiple site collections can be stored in the same content database, so it is important that you include the site collection ID in the query.

How to fix it?

We need to update the SID in the UserProfile_Full table in the Profile database.  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).  That’s not a great solution.

Instead, you can run the Move-SPUser PowerShell command 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 value for SID will be the only net change for the user.  Here’s an example of running this for a single 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

To run Move-SPUser, you will need to be logged on as a farm administrator who also has Full Control permission on the User Profile Service App.  Failure to do so will likely result in a null reference exception.

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.  I have provided a SAMPLE script below that reads the affected user names from a CSV file.

Once you’ve run move-spuser for the problem accounts, you can run the “Identify the Profiles where the SIDs don’t match between UserProfile_Full and UserProfileValue” SQL query against your Profile database again.  If all the users are fixed up, it should no longer return any results.

Important:

If you have a publishing / consuming scenario where you have other farms consuming the User Profile Service Application, you must run the Move-SPUser script on a server in the farm that hosts the UPA.  If it is a dedicated “services” farm, **you may have to temporarily create a web application and site collection in the UPA farm** for the purposes of running the script.  It can be removed after the users have been fixed up.

** See below for a non-web app dependent alternative.

Details about the cause of this issue in case you’re interested:

This SID mismatch situation causes a chain-reaction that I’ll try to explain:

  • 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.  Everything is good.
  • Delete and re-create that user in Active Directory with the same account name.
    • 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 the SID mismatch condition.
  • 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.
    • You see, 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 as far as SharePoint is concerned.
  • 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 the same thing could happen with any feature that uses OAuth, or calls into the UPA to do claims augmentation.  This includes (but is not limited to): Office Web Apps, Workflow, Site Mailboxes, SharePoint-hosted Apps, Provider-hosted Apps, and some custom claims providers).

Example fix script:

Here’s an example script where the problem users account names are stored in a CSV.  The CSV is imported and Move-SPUser is run for each user listed in the CSV.

Notes:
— This assumes that the accounts are in Windows-Claims format.  Ex: i:0#.w|contoso\user1.
— The CSV file should have a ‘header’ called “NTName”, and one account name on each line. This file is pretty easy to build using the output from the “verify you’re hitting the issue” SQL query above.
The input file should look like this:

NTName
CONTOSO\VP1
CONTOSO\jack
CONTOSO\VP2
CONTOSO\usera
CONTOSO\userx
CONTOSO\VP3

############################## -- Script -- ##############################
 #Author: Joroar
 #Date: 9/29/15
 #This script is provided as-is with no warranties expressed or implied. Please have good and current backups.
 #USE AT YOUR OWN RISK!
 #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:\problemUserProfiles.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}
 }
 ############################## -- Script -- ##############################

** No web app alternative **

If you want to run this in a Services farm without any web applications, you can use the $farm.MigrateUserAccount method instead of Move-SPUser.

Here’s an example of migrating a single user:

$farm = Get-SPFarm
$farm.MigrateUserAccount("contoso\user1", "contoso\user1", $false)

And here’s a sample script that takes a CSV file as input and uses the $farm.MigrateUserAccount method to loop through all users in the CSV:

############################## -- Script -- ##############################
#Author: Joroar
#Date: 9/18/18
#This script is provided as-is with no warranties expressed or implied. Please have good and current backups.
#USE AT YOUR OWN RISK!
#Synopsis: Use this to run $farm.MigrateUserAccount against a list of account names stored in a CSV
#$farm.MigrateUserAccount is a farm-wide operation, so it only needs to be run once per-user.
#Just set the top two variables: $path, $logfile
#You MUST run this as a user with Full Control permission on the User Profile Service App
$path = "c:\problemUserProfiles.csv" # The input file with user account names to migrate
$logfile = "c:\MigrateUserAccount-Log.txt" # The output log file
Add-PSSnapin microsoft.sharepoint.powershell -ea SilentlyContinue
$date = Get-Date -Format U
"Started MigrateUserAccount at " + $date + " (UTC time)" | out-file $logfile -append
"===============================================" | out-file $logfile -append
$ErrorActionPreference = "stop"
$csv = Import-Csv -Path $path
[array]$NeedtoFix = @()
$farm = Get-SPFarm
foreach($line in $csv)
{$NeedtoFix += $line}
$fixTotal = $NeedtoFix.Count
for($j=0; $j -lt $fixTotal; $j++)
{
$acc = ""
$acc = $NeedtoFix[$j].ntname
"Fixing user: " + ($j+1) + " out of " + $fixTotal + " --> " + $acc | out-file $logfile -Append
try{
$farm.MigrateUserAccount($acc, $acc, $false) 
write-host "Fixed user: " ($j+1) " out of " $fixTotal " --> " $acc
}
catch [system.Exception]
{"ERROR!!! for user: " + $acc + " -- " + $_.Exception.Message | out-file $logfile -append}
}
############################## -- Script -- ##############################

This method looks simpler.  Why not always use it instead of Move-SPUser?

It may not work well in farms that have more than one User Profile Service app.

More Keywords:

The symptoms can be explained in a few different ways, which I’ll include here to try to increase ‘findability’ of this article.
SID Mismatch
Mismatched SIDs
User lost access
lost permission
Denied access
remove permission
Access Denied
access dropped
permission removed
user deleted