Auditing ProLaw Events with Item Level Security

Friday, March 4th, 2011 | ProLaw Case Management | Scott Randall

It’s sometimes necessary to audit Events in ProLaw that have been secured to a particular Security Group.  Doing so within ProLaw can prove difficult and querying directly against the ProLaw database may be easier.  Of course, before any activities are performed in Microsoft SQL Server Management Studio, be sure to backup and verify the ProLaw database as unfortunate and unexpected issues can arise.  With your ProLaw database backup completed, the following TSQL queries can provide Item Level Security information:

Item Level Security – View Permissions

SELECT EventsNo, RTF, EventKind, ShortNote, EventDate, MatterID
FROM ProLaw.dbo.Events
JOIN ProLaw.dbo.SecurityGroup ON SecurityGroup.SecurityGroup = Events.SecurityGroupView
LEFT JOIN ProLaw.dbo.EventMatters ON EventMatters.Events = Events.Events
LEFT JOIN ProLaw.dbo.Matters ON Matters.Matters = EventMatters.Matters
WHERE SecurityGroup.SecurityGroupDesc = ‘Human Resources’ <=Replace with Security Group Name being audited
ORDER BY EventKind, EventsNo, RTF

Executing the above query in Microsoft SQL Server Management Studio should return the following results:

Item Level Security Events Audit

ProLaw Item Level Security Events Audit

The following TSQL queries may also prove helpful:

Item Level Security – Change Permissions

SELECT EventsNo, RTF, EventKind, ShortNote, EventDate, MatterID
FROM ProLaw.dbo.Events
JOIN ProLaw.dbo.SecurityGroup ON SecurityGroup.SecurityGroup = Events.SecurityGroupChange
LEFT JOIN ProLaw.dbo.EventMatters ON EventMatters.Events = Events.Events
LEFT JOIN ProLaw.dbo.Matters ON Matters.Matters = EventMatters.Matters
WHERE SecurityGroup.SecurityGroupDesc = ‘Human Resources’ <=Replace with Security Group Name being audited
ORDER BY EventKind, EventsNo, RTF

 

Item Level Security – Delete Permissions

SELECT EventsNo, RTF, EventKind, ShortNote, EventDate, MatterID
FROM ProLaw.dbo.Events
JOIN ProLaw.dbo.SecurityGroup ON SecurityGroup.SecurityGroup = Events.SecurityGroupDelete
LEFT JOIN ProLaw.dbo.EventMatters ON EventMatters.Events = Events.Events
LEFT JOIN ProLaw.dbo.Matters ON Matters.Matters = EventMatters.Matters
WHERE SecurityGroup.SecurityGroupDesc = ‘Human Resources’ <=Replace with Security Group Name being audited
ORDER BY EventKind, EventsNo, RTF

Happy querying!

Tags: , , ,

You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

Page 1 of 11

Leave a Reply