How to audit changes that take place in SQL

Everybody will get to a point where they observe changes that take place in a table (rows get deleted , etc.) and can not figure out what is wrong.

If SQL Logging for Config MGr or SQL Profiler does not help (issue occurs during the night, or random) and you can not use the tools mentioned due to the volume of collected data then you can try to set SQL Audit and let SQL record the changes for you.

Let us say , that  you add explicit user with read rights to a view(V_R_System)  and for whatever reason granted users are removed from V_R_System ,every day and you want to troubleshoot.

Here is how you can do it

 

  1. Im SQL-Security-Audits-Right Click- New Audit

Configure settings:

Enable Audit :

You should be able to see:

In SQL-Databases-Security-Database audit specifications-Right Click- Database audit specification…

Configure name, chose Audit and Audit Action Type:Schema Object Change Group

In SQL-Databases-Security-Database audit specifications-Right Click- Database audit specification…

Configure  name. Chose Audit and Audit Action Type:Schema Object Permission Change Group

Enable both

You should see:

Once the issue occured go under SQL-Security-Audits-chose V_R_System- Right Click- View Audit

A window will open

Under  Object Name we can identify the Views

You can export the audit as CSV file

Looking closer you will be able to corelate these actions with a maintenance task (based on the time you should be able to identify this) that drops and creates the views new every day, so you figure out this is by design, but you still need to add those users to the view.
Here is a workaround

In SQL  : Start SQL Server Agent

 

Create a new Job:

Chose a name: