ApexSQL Knowledgebase: How to audit only some users in ApexSQLAudit?

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly.

FAQs

Quick answers to common questions.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools.

Online Documentation

All of our help files for download or convenient viewing online.

Videos

Watch product demos, training videos, or tutorials of our products' main features.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools

How to audit only some users in ApexSQLAudit?

DESCRIPTION
This article discusses how to audit only chosen users with ApexSQL's Audit

SOLUTION
This is possible through changing the Trigger Template. To access the Trigger Template, click on the Template Editor button found on the Outlook Bar Auditing section:

This will start Template Editor:

Next, you should find the part where trigger for INSERT command is created:

'==================================================
'INSERT INSERT INSERT INSERT
'==================================================
caption = "Create Insert Trigger " & "[" & objTable.Owner & "].[" & StartTriggerNameInsert & objTable.Name & "]" & _ ">" for Table " & TableFullName%}
IF OBJECT_ID('{%Audit.Print replace("[" & objTable.Owner & "].[" & StartTriggerNameInsert & objTable.Name & "]", "'", "''") %}','TR') IS NOT NULL
BEGIN
DROP TRIGGER {%Audit.Print "[" & objTable.Owner & "].[" & StartTriggerNameInsert & objTable.Name & "]" %}
PRINT '{% Audit.Print "Trigger Dropped: " & StartTriggerNameInsert & Replace(objTable.Name,"'","''")%}'
END
GO
-- {%Audit.Print caption%}
Print '{%Audit.Print Replace(caption, "'", "''")%}'
go
CREATE TRIGGER {%Audit.Print "[" & objTable.Owner & "].[" & StartTriggerNameInsert & objTable.Name & "]" %}
ON {%Audit.Print TableFullName%}
FOR INSERT
NOT FOR REPLICATION
As
-- "SQLAUDIT GENERATED - DO NOT REMOVE"
-- -------------------------------------------------------------------------------------------
-- Legal: {%Audit.Print Audit.Legal%}
-- Description: INSERT TRIGGER for Table: {%Audit.Print TableFullName%}
-- Author: {%Audit.Print Audit.Author%}
-- Date: {%Audit.Print Now%}
-- -------------------------------------------------------------------------------------------
BEGIN

there you should add following:

IF (SUSER_SNAME() <> N'ComputerName\UserName1') AND (SUSER_NAME() <> N'ComputerName\UserName2') RETURN

Where ComputerName\UserName1 and ComputerName\UserName2 are the user names you want to include in auditing in case they use Windows Authentication on connecting to your server or UserNam1 and UserName2 in case they use SQL Authentication.

These steps you need to repeat for UPDATE (found below this)
'==================================================
' UPDATE UPDATE UPDATE UPDATE UPDATE
'==================================================

and DELETE trigger template.
'==================================================
' DELETE DELETE DELETE DELETE
'==================================================

with the same command:

IF (SUSER_SNAME() <> N'ComputerName\UserName1') AND (SUSER_NAME() <> N'ComputerName\UserName2') RETURN

Then you need to recreate triggers on your tables. First, check tables and columns you want to audit, and then create triggers by clicking on the Create Triggers button:

Script will be built. You just need to execute it:

This will recreate all triggers, and now only users with given names will be audited.

SEE ALSO
How to audit user names in ASP.Net application
How to update ApexSQL Audit without losing custom changes in the architecture
Schedule Audit Report generation
How to Delete Old Data in Audit tables

AUTHOR
Milic Vuletic

LAST REVIEW DATE
19 November 2009

Labels: Apex SQL Audit

posted by Milic Vuletic on Thursday, November 19, 2009

0 Comments: