When a new user is added to a SharePoint Website what happens in background, what are the process that runs, which tables in the DB are updated?
When we give a user permission to the site collection, or when that user creates/updates/deletes any item within a Site Collection, the user will be added to the Hidden User information List which store information in relevant content database [WSS_Content].[dbo].[UserInfo] table.
There are two timer jobs named User Profile Service Application - User Profile to SharePoint Full Synchronization and User Profile Service Application - User Profile to SharePoint Full Synchronization, will update the user profile information in the user profile service application to User information list, through a mapping between the Profile DB and content DB UserInfo table.
More information about User Information List, check the article: