NOTE: DIRECTLY querying any SharePoint SQL database is NOT SUPPORTED (Except the Logging Database). If testing the query below I’d recommend backing up and restoring the content database to a different SQL Server (Non SharePoint and Non Production) and running through this.
Now that we got the disclaimer out of the way, here is the background – I was working with Microsoft on a support issue which involved user accounts not showing up in the User Information List correctly. Here’s a SQL script that Microsoft provided that helps query this information in SQL.
Replace the sitecollectionurl with the site collection URL and ADAccount with the samAccountName in question. This will return quite a bit of information about that user in the specified site collection.
Declare @UserInfoID UniqueIdentifier
Declare @SiteID UniqueIdentifier
Declare @url nvarchar (100)
-- !! Change only the url below to the relative URL you are querying !!
set @url = 'sites/sitecollectionurl'
Select @SiteID=w.SiteId, @UserInfoID=s.UserInfoListId
from AllWebs W (nolock) join AllSites S (nolock)
on s.Id = w.siteid
where w.fullurl = @url
select ui.tp_id, ui.tp_login as UserLogin, ui.tp_title as UserName, ui.tp_email, UI.tp_Deleted, UI.tp_isactive, UI.tp_domaingroup, ui.tp_token, ui.tp_externaltoken, aud.*
from AllUserData AUD (nolock)
join userinfo (nolock) UI
on AUD.tp_id = UI.tp_id and ui.tp_SiteID = aud.tp_SiteId
where AUD.tp_ListId = @UserInfoID and UI.TP_Login Like '%ADAccount%'
order by ui.tp_id