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.

SELECT distinct
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.

res.UserAtHost as “SIP Address”,
hud.LastNewRegisterTime as “Last Logon”
rtcdyn.dbo.HomedResourceDynamic hud
(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


Posted by Albert Hoitingh

I'm an Office 365 businessconsultant/architect. My focus is on Office 365, information-management, security and governance. I'm honored to be a Microsoft MVP. I like to present and share information, most recently @ SharePoint Saturday London, Cambridge and Lisbon.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s