SharePoint deep dive exploration: looking into the SharePoint UserInfo table

A couple of months ago we encountered some issues after upgrading a SharePoint 2007 environment to SharePoint 2013 using a migration tool. One of the symptoms was that information about users was incorrectly displayed. This led us into the looking into how SharePoint stores user information inside its databases.

The user information which is being displayed in a created by or a modified by field in SharePoint it is not being directly retrieved from Active Directory but it is retrieved from an internal SharePoint table called the UserInfo table.

All users in Active Directory are not immediately added to this table. When a user is explicitly added to a site collection using security settings, it is added to the UserInfo table. Another way that user info is created in this table, is when a user  is granted access through an Active Directory group and the user visits the site for the first time.

Users which are deleted from  a site collection, will still be found in the UserInfo table but with a flag bDeleted set to True (1).  When the people picker queries the UserInfo table, it will not include user with bDeleted set to 1. The All people page ( /_layouts/people.aspx?Membershipgroupid=0) will also only list users where bDeleted equals 0. This also means that even when people leave your organization and their Active Directory account is disabled (or removed), the Created By and Modified By columns will still display the name of the user. The general recommendation is to leave this mechanism as it was designed but there are border case scenarios in which you want to delete users – if so you can take a look at Delete users and clean up user information list in SharePoint

There also are two different timer jobs which synchronize information from the User Profile Service Application to all site collections:

• User Profile to SharePoint Quick Synchronization – runs default every 5 minutes – synchronizes information for users recently added to a site collection
• User Profile to SharePoint Full Synchronization – runs default every hour -

(For a full list of all out of the box timer jobs in SharePoint Server 2013 check out SharePoint Server 2013 – Timer Job reference) These job will only synchronize users where the tp_IsActive Flag is true(1) in the UserInfo table. The reasons for this is performance since synchronizing all users would be quite resource intensive. tp_IsActive is set to true when a user first visits a site collection or when he is granted Contribute permissions explicitly on a site.
References:

• Display name in SharePoint is out of sync
• Delete users and clean up user information list in SharePoint  - border case scenario only apply if you are in the same situation.