Using SQL databases for enumerating users

Always wanted to know which users access your SharePoint or OCS platform, but want to use the out-of-the-box possibilities? Why not use the SQL databases.  The next queries will help you with this. You will need access to the SQL Server and the databases of course.

SharePoint
SELECT distinct
tp_Login,tp_Title,tp_Email,tp_siteAdmin,tp_siteid,tp_externaltokenlastupdated,tp_isactive
FROM
WSS_Content_db.dbo.UserInfo,
WSS_Content_db.dbo.Webs
where
webs.siteid=userinfo.tp_siteid
and tp_deleted=0
and tp_login<>’NT AUTHORITY\Local service’
and tp_login<>’NT AUTHORITY\authenticated users’
and tp_login<>’SHAREPOINT\system’
order by tp_isactive, tp_login

Check the name of the database (WSS_Content_Db) for the correct one!

Office Communications Server R2

This query will only work with Office Communications Server 2007 R2.

select
res.UserAtHost as “SIP Address”,
hud.LastNewRegisterTime as “Last Logon”
from
rtcdyn.dbo.HomedResourceDynamic hud
join
(Select ResourceId, UserAtHost from rtc.dbo.Resource
group by ResourceId, UserAtHost)res
on hud.OwnerId=res.ResourceId
order by “Last Logon”

For OCS R1, use this database-name: rtcdyn.dbo.HomedUserDynamic as Microsoft changed this from R1 to R2

Enjoy!