SharePoint 2016 Restore User that was deleted from User Information List

There could be a scenario when the user was deleted both in AD and the User Information List and we need to restore it. That could be necessary when there was a custom solution developed that relies on the user accounts to exist in SharePoint. Needless to say these solutions were developed incorrectly, but who am I to judge.

For these rare cases, here is the way that to restore the deleted user. This worked for me in SharePoint 2016, but it might also work in 2013 and 2019.

Disclaimer: Any direct modifications to the SharePoint SQL databases are not supported by Microsoft. Restore the user by following approach only if you know for sure what you are doing.

1. First of all - determine an ID of the deleted user:

2. In SQL Management Studio - find your content database and navigate to the UserInfo table. Verify that deleted user is still listed in the table. Notice that tp_Deleted will equal to the user ID and tp_IsActive will be equal to 0:

3. Modify a row where tp_id is equal to the user's ID:
 a) set tp_Deleted to 0
 b) set tp_IsActive to True
 c) Save changes to the row

4. For the appropriate content database, run the following command to find deleted user in the  AllUserData table:

  
  SELECT * FROM [WSS_Content].[dbo].[AllUserData] 
  Where bit3 = 1 and tp_ID = User_ID

Make sure this command returned a single row. If there was a single row - proceed to restoring the user:

5. To restore the user, we need to change bit3 column's value from 1 to 0 :

 
 
 UPDATE [WSS_Content].[dbo].[AllUserData] 
 SET bit3 = 0 
 Where bit3 = 1 and tp_ID = User_ID

Done! Now go ahead and click on the deleted user. Verify that there is no error.