Overcoming Limitations on MS SQL Server Triggers on SELECT Queries

Since SQL Server serves as the back end for many business-critical applications, it is vital to promptly spot any improper change or access event that could indicate a security breach. One of the most common ways to implement simple auditing on SQL servers is to use triggers.

The bad news is that there is no way to fire a trigger on SELECT in SQL Server, so you can’t use triggers to see who accesses and reads tables. Specifically, SQL Server allows you to create triggers on the following three types:

• DDL triggers on ALTER, DROP and CREATE table or database statements
• DML triggers on INSERT, UPDATE or DELETE statements on a table or view
• Logon trigger fires on LOGON events

The good news is that although you cannot create a SELECT trigger in SQL Server, there are other ways to track access events in Microsoft SQL Server. One option is to use the native SQL Server audit logs. Unfortunately, however, they contain so much noise that it’s hard to single out the events you want to see. In addition, native SQL Server auditing may significantly impact your server’s performance. Therefore, you will be much better off using a lightweight specialized auditing solution like Netwrix Auditor. Netwrix Auditor delivers full visibility into who is doing what in your SQL Server instances — including who is accessing what data — with much less of a performance hit on your SQL databases.

Automate Auditing of SELECT Statements

One of the key benefits of Netwrix Auditor is that it overcomes the limitation of SQL triggers by reporting on successful SELECT queries — empowering you to see exactly who has read data from which SQL tables. Instead of using triggers and stored procedures, you can simply review database access using clear reports and easy search, and you can also set up custom alerts built on the same reports and searches. With this comprehensive information readily at hand, you can prevent leakage of sensitive data, speed security investigations and prove to auditors that only authorized users are viewing regulated data. Plus, because Netwrix Auditor uses triggerless data collection, it doesn’t affect database performance as much as native tools.

Additional Benefits

Beyond SELECT statements, Netwrix Auditor also reports 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.

On top of this, 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 on them, ensuring you efficiently get the information you need with surgical precision.