How do I audit only some users in ApexSQL Trigger?

Applies to
ApexSQL Trigger

Summary
This article discusses how to audit only some of the users with ApexSQL Trigger rather than all of them, and how to exclude certain users from auditing

Description

By default, triggers creation template in ApexSQL Trigger does not contain checks for the user performing the DML operation (INSERT/UPDATE/DELETE) in audited table. This means that, by default, after creating auditing triggers for specific table using ApexSQL Trigger, DML changes from all users will be tracked and information stored in the auditing tables

Most of the times such behavior is desirable, but there might be a table in your database for which you want to track changes only from some users (not all of them), or you want to exclude some users from change tracking, to reduce the amount of collected information and ease the data filtering in the auditing reports

ApexSQL Trigger lets you apply necessary modifications in trigger creation template in just a few steps using the Template editor

In the following example, we will modify the trigger creation template to include/exclude INSERT operations from auditing for 3 users. The same modifications can also be applied for other two DML operations (UPDATE/DELETE) in the similar manner

First, from the application’s main window select the tables, fields and DML operations for which you want to modify template and create auditing triggers

Next, open the Template editor by clicking

Edit template button from the Triggers section in the Home tab

Inside the Template editor, perform the steps shown in the image bellow:

1. Initiate the search using CTRL+F, and in the search field enter

   “INSERT TRIGGER for Table”

This is where the template for the INSERT trigger begins, so the modifications will be applied here

2. Bellow @ROWS_COUNT variable, add declarations for the following variables:

   @user SYSNAME
   	,@var1 AS NVARCHAR(128)
   	,@var2 AS NVARCHAR(128)
   	,@var3 AS NVARCHAR(128)
   

3. Assign the variables declared in the previous step as follows:

SET @user = CAST(SUSER_SNAME() AS NVARCHAR(128))
SET @var1 = 'MJFORD'
SET @var2 = 'KPDUFFY'
SET @var3 = 'RWALTERS'

Variables @var1, @var2 and @var3 contain the usernames against which we’ll perform the check before storing the information in the auditing tables. Variable @user points to the actual user performing the INSERT operation for which the trigger is fired.

In this example we are declaring three variables, each for the one of three users. You may declare and assign as many variables as there are users you want to check for, before auditing the change

4. Finally, after creating and assigning variables, add the IF statement that performs the check:

IF NOT (
		(@user = @var1)
		OR (@user = @var2)
		OR (@user = @var3)
		)
	RETURN

With this check, INSERTs made by only these three users will be audited. INSERTs from all the other users will be ignored

If you want the opposite: to exclude INSERTs made by these three users, but audit INSERTs from all the other users, then the IF statement needs to be changed as follows:

IF (
		(@user = @var1)
		OR (@user = @var2)
		OR (@user = @var3)
		)
	RETURN

Note that the difference between these two IF statements is that the NOT keyword is removed from the latter one



After applying the necessary modifications, save the template by the name ApexSQL2053_custom.audx, and close the Template editor

Finally, you need to create the triggers using the modified template. To do so, click on the Create button under the Triggers section in the Home tab



This opens the Script window containing the actual script for the triggers that will be executed. Here, you can again review the modifications. After review, click the Execute button to create the auditing triggers, as shown on the image bellow



Once this is done, auditing triggers will be created and the auditing of the INSERT statements on the selected table will be enabled only for the selected users

You can modify the template in the same manner to audit UPDATE and DELETE statements from certain users only

So, for the UPDATE, change the template as follows:



And, for the DELETE trigger:



About ApexSQL Trigger
ApexSQL Trigger is a SQL Server auditing tool, which tracks data changes in SQL Server databases using triggers. ApexSQL Trigger allows you to audit database access by login, host and application name

Related posts:

1. How to manage triggers in ApexSQL Trigger
2. A high level overview of data auditing with ApexSQL Trigger
3. How do I choose between ApexSQL Trigger and ApexSQL Log?
4. Using the template editor in ApexSQL Trigger
5. Installing audit tables on another database