Overcome MS SQL Trigger Limitations to Monitor SELECT Queries

Download Free 20-Day Trial

Since SQL Server stores valuable data and serves as the back end for many business-critical applications, any improper change or access event could lead to a security breach. Therefore, you need to closely monitor access to your Microsoft SQL Server and activity around the sensitive data it holds.

Unfortunately, native SQL Server audit logs contain so much noise that it’s hard to single out the events you want to see: the critical changes that pose a threat. In addition, using native SQL Server auditing significantly impacts your server’s performance. To avoid these issues, you can use SQL triggers instead of native SQL Server auditing.

Understanding SQL triggers

A SQL trigger is a special type of stored procedure that fires (automatically runs) when a particular type of event occurs in a database. There are three types of SQL triggers:

  • DDL trigger — Runs when a DDL (Data Definition Language) event occurs. DDL events include ALTER, DROP and CREATE table or CREATE database statements, as well as certain system stored procedures that perform DDL-like operations.
  • DML trigger — Runs when a user tries to modify data through a DML (Data Manipulation Language) event. DML events are INSERT, UPDATE or DELETE statements on a table or view. DML triggers fire when any of these events occurs, whether or not table rows are affected.
  • Logon trigger — Fires in response to the LOGON event that is raised when a user's session is being established.

Similar to native auditing, triggers add processing overhead to database systems, which can make them run slower.

The key limitation of SQL triggers

SQL Server lets you create triggers for any statement except one — there is no way to fire a trigger on SELECT in SQL Server. In a nutshell, this means that you can’t use triggers to see who accesses and reads tables with sensitive data.

How Netwrix Auditor can help

Netwrix Auditor overcomes the limitation of SQL triggers by reporting on successful SELECT queries, which empowers you to see who has read data from which SQL tables. You can review database access using both reports and search results, and you can also set up custom alerts. Having this information at hand will help you prevent leakage of sensitive data, speed security investigations and prove to auditors that only authorized users are viewing regulated data.

Plus, because it uses triggerless data collection, Netwrix Auditor is less invasive than native auditing and doesn’t hurt database performance.

Here’s an example of the detailed search results on reads of SQL Server data:

Beyond SELECT statements, Netwrix Auditor also report and alerts on other SQL queries, such as INSERT, UPDATE and DELETE. As a result, you can hold your privileged users accountable, provide reports to managers much faster and prove to compliance auditors that you have the required controls in place to protect your sensitive structured data.

Plus, Netwrix Auditor provides granular control over the monitoring scope. Using the versatile inclusion and exclusion filters, you can select certain databases and the specific actions that you want to monitor for them, ensuring you efficiently get the information you need with surgical precision.