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.
ApexSQL Trigger is an auditing tool for SQL Server databases, which captures the data and schema changes that have occurred on the database, including the information on who made the change, when, which objects were affected by it, as well as the information on the SQL login, application and host used to make the change. It stores all captured information in a central repository and exports it in print friendly formats.
To audit your SQL Server database and see who is deleting the data, when and using which application and computer:
-
Start ApexSQL Trigger
-
In the Main window click the New button to create a new project
-
Connect to the database you want to audit:
The user needs to have at least the db_datareader role on the msdb database and be a db_owner of the audited database
-
If the database auditing architecture isn’t installed, you’ll be prompted to install it. Click the Yes button, when prompted to install, since without it database auditing is not possible. The auditing architecture contains database objects that enable database auditing – stored procedures, functions, views and tables – that capture the database changes, store them and extract for reporting:
-
The list of the scripts and objects that the architecture installs is shown. Click the Install button to add the auditing architecture to the audited database:
-
Select the table you want to audit in the Main grid. For example, select the CreditCard table if the CreditCardID values are disappearing
-
In the Columns pane, check the columns you want to audit:
The table in the Main grid will be designated as Prepared
-
To audit only the delete operations of the entire row, uncheck the Insert and Update columns for the CreditCard table. To audit the delete operations of a specific column, check Update, as the column is actually updated to a NULL value
-
Click the Create option on the Home tab, of the Triggers group to create triggers
-
The DDL script that creates triggers for the CreditCard table is shown in the ApexSQL Trigger internal editor. To create the triggers, click the Execute button in the menu, or press F5:
You’ll be notified that the triggers were successfully created:
The CreditCard table will be marked as Audited in the Main grid:
To show how this actually works, several credit card numbers were deleted using SQL Server management Studio with a personal login.
Then ApexSQL Trigger was started and a standard report was generated, filtered by the CreditCard table to narrow down the results. The results showed: the user names, machines and applications used, the time the records were changed, and both the old and the new values:
Auditing a database and tracking database changes doesn’t have to be a time-consuming task of monitoring and waiting for the incident to occur. Just open the ApexSQL Trigger standard report and see when, how and who has been tampering with your data.
April 4, 2013
