SharePoint: Users randomly lose permission – are deleted from site

Posted On December 30, 2018

Update 11/16/22: I’ve just tested this in the latest (November 2022) builds of both SharePoint 2019 (16.0.10392.20000) and SharePoint Server Subscription Edition (16.0.15601.20226). The problem still occurs in those builds.

This is a good one. It appears to be both random and intermittent (it’s actually neither), 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.

You may find entries like the following in your SharePoint ULS logs at the time the user lost permission:

Failed to add [Login=i:0#.w|contoso\user1] to the database. It may already exist at [SiteId=5F39AB50-AD42-4B35-85E3-3BDF60028A7B][UserId=0]. HR=0x80070050

Found a user with the same tp_Login but different tp_SystemId. Try to delete it. Site ID: 5F39AB50-AD42-4B35-85E3-3BDF60028A7B tp_Login: i:0#.w|contoso\user1

Cause:

At some point, the user had been imported by User Profile Synchronization (Profile Sync), deleted from Active Directory (AD), 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 in the Profile database.  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 users 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.  It is unsupported to do that with a direct SQL update. One way to do this in a supported fashion would be to delete all the of the problem user profiles and re-import them.  However, those users would lose all manually entered profile data like “About Me”, “Ask me about”, “Skills”, “Interests”, etc.  That’s not a great solution in most cases.

Instead, you can run the Move-SPUser PowerShell command to update the SID in the UserProfile_Full table to be the “good / current” 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: Object reference not set to an instance of an object.

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 could either temporarily create a web application and site collection in the “services” farm for the purposes of running the script, or you could use a lesser known equivalent to Move-SPUser: $farm.MigrateUserAccount. Example:

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

This method is nice because unlike Move-SPUser, you don’t have to specify a site that the user exists in.

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 / Import.
    • 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.
  • Disable the user in Active Directory, and run a Full Import.
    • Assuming you have chosen to filter out disabled users in the import connection, they will become a non-imported profile.
  • Delete that user account in Active Directory.
  • Recreate the user in AD. Use the same account name (e.g: contoso\user1).
    • They will have the same account name, but a new SID.
  • Run another Profile Sync / Import.
    • 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 within 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 / or loses all individually-given permissions, and must be added back to site permissions.
  • The user is added back to the site permissions.
    • 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.

Important: The above example 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, 2013-platform Workflows, Site Mailboxes, SharePoint-hosted Apps, Provider-hosted Apps, and some custom claims providers.

Example fix script:

Here’s an example PowerShell script where the problem user account names are stored in a CSV.  The CSV is imported and $farm.MigrateUserAccount is run for each user listed in the CSV.

Note:
— 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/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 -- ##############################

Avoiding this problem:

It would be nice if Active Directory admins didn’t delete and recreate accounts with the same account name, but this can be avoided by simply keeping on top of your user profile cleanup. It’s pretty common for admins to first disable an account in Active Directory for a period of time before deleting it. If you are filtering out disabled users from profile import, and periodically running through profile cleanup steps like you should be, the defunct user profiles will be deleted from the profile database. Then, when the account is re-created in AD, it will get a brand-new record in the UPA, and everything will be fine.

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

Add a Comment