How to Create a SQL Server Audit Trigger


Native Auditing vs. Netwrix Auditor for SQL Server

Native Auditing Netwrix Auditor for SQL Server
Steps
  1. Create an audit table with columns equal to the audited database that to record the changes: for example, we need to audit table named Sales 1 with columns -  ContactID, DateAquired and ContactStatus.

    create table AuditTable1
    (
    AuditID integer Identity(1,1) primary key,
    ContactID integer,
    DateAquired datetime,
    ContactStatus varchar(20),
    WhoChanged nvarchar(128),
    WhenChanged datetime

    go

  2. Create Audit trigger  by running the following script: (please, note that we should join tables by public key - in this case it is ContactID). 

    create trigger AuditTrigger1 on Sales1
    after update, insert
    as
    begin
    insert into AuditTable1
    (ContactID, DateAquired, ContactStatus, WhoChanged, WhenChanged)
    select i.ContactID, i.DateAquired, i.ContactStatus, SUSER_SNAME(), getdate()
    from Sales1 t
    inner join inserted i on t.ContactID=i.ContactID
    end
    go

  3. After we created a trigger, all changes will be recorded in the newly created table. You can view all changes in the table (AuditTable1) by executing this simple query:

    Select * from AuditTable1 order by WhenChanged

Report sample:

  1. Run Netwrix Auditor → Select “Reports” → choose “SQL Server” → Select "All SQL Server Data Changes" report → Click "View". 
  2. Subscribe to this report and receive it via e-mail or have it delivered to a specified shared folder according to the schedule you set. 

Report sample:

Go Beyond Creating an SQL Server Audit Trigger for INSERT or UPDATE Tables to Abate Threats to Your Critical Data

SQL Server is one of the most critical systems in the IT infrastructure, storing valuable data and serving as the back end for many business-critical applications. Because even a small inappropriate action to a table with sensitive data, such as an update or insert, can lead to disruption in data integrity, tracking changes to SQL Server tables must be a top priority for database administrators. Native tools can help you create SQL Server audit triggers — for example, you can create a trigger to track changes to a table, such as data insertion, update or deletion. However, keep in mind that in order to create any SQL Server audit trigger that logs changes to your audit trail, you need to be fluent in Transact-SQL. Plus, you will need to create similar but distinct triggers for each table you need to track in order to meet audit specifications. 

There’s an easier and better way to keep an eye on changes to tables, schemas and more than by manually creating SQL Server database audit triggers or DDL triggers and painstakingly crawling through cryptic data. Netwrix Auditor for SQL Server maximizes visibility into what’s happening across your SQL servers, including what changes were made to your sensitive information, who made them and when. Forget about setting up multiple SQL Server audit table triggers — the solution collects and consolidates changes from all your tables and databases automatically. The prebuilt reports enable you to easily spot changes and the Interactive Search feature makes it easy to investigate aberrant changes, so you can proactively catch data threats before it’s too late. 

 

Join the discussion