Auditing User Activities in SharePoint 2007

Scope

This documentation is to find or trace user activities on SharePoint website

Purpose

When we enable Audit Logs in SharePoint, we can track all the events related to additions and deletions of users/groups in SharePoint.

We can trace the same events using SQL queries queried on site content database as a SharePoint Administrator in more detailed manner.

Steps for Auditing

Below are the queries which have been used to trace the user deletions:

1.       Get the group ID and Site ID from below query

SELECT * from Groups with (nolock) where Title=’GroupName’;

2.       Get all deleted instances of that particular site scope from the below query…

SELECT * from AuditData with (nolock) where EventData like '<roleid>-1</roleid><principalid>’Principal ID’</principalid>%' and ItemType=6 and Event=’Event Id’;

3.       Get the User details with the ID ‘Owner ID’ using the below query...

SELECT * from UserInfo with (nolock) where tp_ID=’Owner ID’;

4.       Get the permissions which are deleted in the above Site Scope by USER from below Query…

SELECT * from dbo.Perms with (nolock) where ScopeId=’Scope ID';