How to automate adding SQL auditing triggers to a new, or changed database

Maintaining a before-and-after audit trail for sensitive tables can be time consuming, especially with a database that is under continuous development, and particularly in teams that use continuous integration. Most table changes will break existing triggers and necessitate their update. The ability to automate the refactoring and re-creation of a trigger based auditing layer, to keep up with underlying database changes, and run this process unattended or as part of a continuous integration process can be a huge time saver.

September 28, 2016

How to automate trigger-based SQL Server auditing

More often than not, a top level requirement for all SQL Server DBAs is to have mechanisms to capture who changed what and when and to have stored audit information available for further investigation. In order to achieve this, database auditing needs to be set up using various mechanisms. One of those mechanisms is a trigger-based auditing.

April 27, 2016

SQL Server database trigger auditing – How to customize data tracking and reporting – Part 1

ApexSQL Trigger uses SQL Server database triggers for capturing and storing changes that occur on the audited data and schema. More specifically, the DML database triggers are used for capturing INSERT, UPDATE or DELETE statements, and DDL trigger is used for capturing CREATE, ALTER or DROP statements. Now, since there are various types of columns in each database table, reading the audit report is often not very user-friendly and this especially applies to foreign key values in different tables.

March 12, 2015

SQL Server database auditing techniques

SQL Server database auditing is not used only to address auditing requirements for compliance. It has become necessary for the analysis of database actions, troubleshooting problems, investigating suspicious and malicious activity. It can also help preventing users from inappropriate actions – as if you had a CCTV system on your databases

There are several SQL Server auditing techniques:

June 28, 2013

Auditing triggers in SQL Server databases

One of the essential SQL Server security topics is finding out who did something, what and when. Being able to provide a history of data for various auditing purposes, some of which are enforced by US laws through compliance regulations, is a serious task for any DBA. Since business policies or the compliance regulations require the auditing of data changes within a database, various techniques adapting to the requirements are used to perform database auditing. The common one is using auditing triggers in SQL Server databases.

June 26, 2013

Get an alert when a certain record changes in SQL Server

Auditing a database is the first step towards staying updated about database changes. However, if specific, highly sensitive data needs reviewing, an immediate notification of any change is preferable.

Sending email alerts to one or more recipients is one of the most common ways of immediate notification. Sending emails for every change in a database is not advised. It will, in most cases, quickly fill your inbox, and add many unnecessary actions, which can also affect the overall application performance. Therefore, it is important to choose the changes that you want to be alerted about, carefully.

April 4, 2013

Audit SQL Server database and see who deleted a column value

Every DBA and developer strives to be in control of SQL Servers, databases and data. But, sometimes the situation can get out of control and unusual things start happening.

For example, you have noticed that a value from a specific table column in your database has been disappearing. You’ve checked your code as much as possible and didn’t find anything that deletes the column value, the users say they are not deleting it, you’ve checked the permissions on this table/column and found that no unauthorized deletes were allowed. But something is obviously wrong.

April 4, 2013

How can I make my custom applications self auditing?

SQL developers are increasingly required to include tracking data changes into their custom applications. The information about what change was made – insert, update or delete – who made it, when, and also which computer and application was used, is very useful and can be essential for troubleshooting.

April 4, 2013