Auditor New Features, Part 1: Finding Auditor Triggers in a SQL Server Profiler Trace

Jan 25, 2016

In Auditor, there is a powerful feature called SQL Table audits.  These audits will capture database changes that occur either from inside Microsoft Dynamics GP, changes that occur as a result of an external process, or even someone making changes directly in SQL Server Management Studio.  The way Auditor does this, behind the scenes, is by using SQL database triggers.

A SQL Server Profiler trace is often an invaluable tool when troubleshooting problems.  The only problem is, the execution of a SQL database trigger it is not often obvious—unless you happen to be familiar with the exact code that is contained within that trigger.  For this reason, we have changed Auditor’s SQL database triggers to include a statement at the beginning that contains the trigger name.  This line of code will look something like this:

DECLARE @TriggerName CHAR(80) = ‘RSAS_TWO_GL00100_INSERT’


That way, you can simply do a search for the name of the trigger in question and you will know right away when, or even if, it executed.

In order to take advantage of this feature, first be sure that you are using at least Auditor build 2013.7, 2013 R2.1008, or 2015.4.  Once you install any of these builds, the Install Wizard should automatically rebuild all of your existing SQL Table audits so that the triggers contain the new code.  Then, when you create a trace file using the SQL Server Profiler tool, be sure to click the Events Selection tab, then click the “Show all events” box.  Now, look for the Stored Procedures section and be sure that SP: StmtStarting and SP: StmtCompleted are selected.  When you run the profile, if you cannot find anything that contains “RSAS_”, then you know that no Auditor triggers were executed.

Click here to watch a short overview of the 3 main new features in the January 2016 Auditor release.

Send this to a friend