"Inconsistency" between user information in SharePoint sites and AD user information

First, describe the problem: the ad user "user1" has been added to the SharePoint site, and then perform the following operations: Delete "user1" from the SharePoint site, delete "user1" from AD, add a new user "user1" to AD, and add a user "user1" to the SharePoint site, you will find a very interesting problem: You may successfully add this user, but this user cannot log on to the SharePoint site at all times; or you cannot add this user to the SharePoint site at all, this user already exists on your site.

Some netizens asked similar questions during the last SharePoint technology chat on the csdn site. At that time, I could not give a very detailed explanation during the chat, so we just provided a related link. I will explain it in detail in this article today.

This problem occurs because of the storage mechanism of SharePoint for site users. SharePoint site user information is stored in the userinfo table of the site's corresponding content database. If the site administrator deletes a user on the site, you may be surprised to find that, the user's corresponding records are not deleted from the userinfo table, but the data in the "tp_deleted" column is set. (The reason for this mechanism is that this record may have been associated with other records in other tables through foreign keys, therefore, it is not advisable to delete the record directly .)

Later, if the site administrator adds a user with the same name to the site, because the userinfo table already has this user record, so SharePoint only sets the data in the "tp_deleted" column of this record.

At this time, the problem arises. In the userinfo table, there is a "td_systemid" column, which is used to record the Security Identification Number (SID) of this user. We delete a user in AD and add another user with the same name. the SID of these two users must be different. SharePoint uses the "td_systemid" column to identify users. Because the SID of the two users must be different, the SharePoint site "does not recognize" the User Added in AD.

The solution is to synchronize the data in the "td_systemid" column of the userinfo table with the user information in the current active directory. There is a suser_sid () function in sqlserver to return the SID information of a user. On Dean's blog, he has provided a complete SQL statement, which can be directly used.

By the way, on deam's blog, he also described another problem caused by this problem: if we back up the site, we also backed up the user information together, when recovering to another ad, if the site user has the same name, it will also cause Sharepoint to "disrecognize" the ad user.