SharePoint: Users randomly lose permission - are deleted from site

  • [アーティクル]

This is a good one, it appears to be random, and intermittent, and is extremely hard to track down.

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 more friendly 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, 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 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:

-- 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

-- 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 as HEX.  You'd have to use some PowerShell to decode them to the familiar "S-1-..." format, but that is unnecessary for our purposes.

-- 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 https://YourSiteURLHere).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 real change for the user.  Here’s an example of running this for a single user:

 $url = "https://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 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).

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”.  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 =  "https://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 more simple.  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.
User lost access
lost permission
Denied access
remove permission
Access Denied
access dropped
permission removed

user deleted

Comments

  • Anonymous
    August 24, 2017
    Many thanks for this very comprehensive article. It has answered some very old mystery on lost permissions.
  • Anonymous
    January 19, 2018
    Speaking of weird permission errors in SharePoint, I remember facing one issue when the site collection quota has been hit. Because users are saved in an internal list the first time they access the site collections, reaching the site quota means that that operation cannot be done anymore. New users will get an access error if they try to access the site, even if they were granted permission through AD groups.So remember to check the site quota if you find one of those strange permission issues.
    • Anonymous
      January 21, 2018
      That is correct. When users are given permission via AD group, they have permission to the site, but are not added to the User Information List until the first time they actually visit the site. If the site is at storage quota or is locked as read-only, the operation will fail. Check Central Admin | Application Management | Configure Quotas and Locks.
  • Anonymous
    February 13, 2018
    Thanks you so much, you spare me hours of research and fixing
  • Anonymous
    May 24, 2018
    Great explanation.
  • Anonymous
    January 11, 2019
    One of the best article I have ever read. Mysteries explained in very well detailed fashion. Thank you SPJR.