Auditing using SQL Server Triggers

Auditing your SQL Server instance can be done in many ways out of which one of the ways is using auditing triggers. This article explains how to conduct SQL database auditing using SQL Server triggers.

What is a SQL Trigger?

A trigger is a stored database procedure that gets automatically invoked when a specific event occurs in your database. There are four types of triggers:

• DDL triggers
• DML triggers
• CLR triggers
• Logon triggers

DDL triggers.

DDL triggers are fired in response to Data Definition Language (DDL) events. The CREATE, ALTER, DROP, GRANT, DENY and REVOKE transact-SQL statements can invoke DDL triggers. The invoked DDL triggers can perform one of the following functions:

• Execute a set of procedures when there is a modification in your database schema.
• Record the changes carried out using these statements in another table.

DML triggers.

DML triggers are the procedures executed when Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE are executed. These triggers help in auditing by automatically creating tables called INSERTED and DELETED to record the values before and after the modification of your database.

CLR triggers.

CLR triggers allow you to create objects in your SQL instance that can have a reference object outside your SQL Server. This is a special type of trigger that can be inserted directly in .NET language codes and is available in SQL Server 2008 and above.

Logon triggers.

The logon triggers are fired when a SQL Server LOGON event occurs. Logon triggers can be used to track login activities, regulate login attempts, and restrict logins to critical SQL instances.

Are you auditing your SQL Server effectively?

Besides using SQL triggers, auditing SQL Servers can also be done using other methods such as SQL Server Trace and Profiler, DB audit specification, Server audit specification, and C2 auditing. Though auditing SQL Servers using triggers is efficient to a certain extent, there are some major shortcomings to this method. Triggers can be created only for individual tables. So if there are 100 tables in your database that need to be monitored, then the admin needs to create 100 triggers. This is a major operational bottleneck. The amount of time and effort to create triggers for auditing is huge. Therefore auditing can't entirely rely on triggers and also admins who manually audit DB should carefully choose this platform.

The information recorded using SQL Server triggers don't provide insights about any incidents or attacks taking place in your SQL Server. Alternatively, you can use a log management solution to effectively audit your SQL Server logs.

EventLog Analyzer is an effective log management solution that collects all the SQL Server logs, analyzes them using it's powerful correlation engine and provides intuitive reports for all DDL, DML and security-related events taking place in your database. It can provide reports for critical events like privilege abuse, sensitive data leakage, account lockouts, storage media exposure, SQL injection and Denial of Service to name a few. You can set up alerts for any of these events to get notified in real-time through email and SMS. Check out other reports EventLog Analyzer provides for SQL Server auditing.