Sql Server Schema Auditing

SQL Server database schema and compliance audit tools

There are a lot of places from where you can start. Firs of all, let's start with the Brent Ozar's blitz routines:

• sp_Blitz
• sp_BlitzCache
• sp_BlitzFirst
• sp_BlitzIndex

You can download the pack, there is documentation, GitHub repository and some demonstration videos. Execute them and explore the results.

The above is more about performance and if you download (and you should always use the latest version) the SSMS 17.4 you can run a Vulnerability Assessment. More details can be found here, but basically you can check:

• Meet compliance requirements that require database scan reports.
• Meet data privacy standards.
• Monitor a dynamic database environment where changes are difficult to
  track.

It is new report and new things are coming as availability to schedule the report and new checks. You ask for features in the comments sections in the article.

---

In the past I have used a little bit SQL COP tests. You can check them, but I believe the Brent Ozar's pack is up-to-date, better and the choice here (but you can check if you want).

Global Audit Table in SQL Server

Logging DML with database level trigger on big data and bulk insert/update/delete have a performance issue, so there is three other option :

1. SQL server Change Tracking : more info
2. Build-in data changes function (CDC) : more info
3. Database level Audit Log : more info

And i strongly preferred option no 1.

How to audit SQL Server schema updates

There is no standard way to see this data but it might be possible using 3rd party tools. If your database was in full recovery mode then you can try reading transaction log using 3rd party reader such as ApexSQL Log or Quest Toad. ApexSQL Log specializes in log reading and has more options for this while Toad is similar to SSMS and has many other options for general database management.

How can I do dml auditing on SQL server?

I guess that I fogot to mention that we have sql server 2008 , so I found that most of the features were introduced to 2012 and above , so the solution found is:
CREATE DATABASE AUDIT SPECIFICATION referring to the link
https://msdn.microsoft.com/en-us/library/cc280404.aspx

Regards,

thank you all

How to keep an audit/history of changes to the table

There are two common ways of creating audit trails.

1. Code your data access layer.
2. In the database itself using triggers.

There are advantages and disadvantages to both. Some people prefer one over the other. It's often down to the type of app and the type of database use you can expect.

If you do it in your DA layer it's pretty much up to you. You just need to add code to every method that saves to the database to also save a log of the changes. This auditing code could be in your DA layer code, or even in your stored procs in your database if you are using stored procs for everything. Essentially the premise is the same, any time you make a change to the database, log that change.

If you want to go down the triggers route, you can write custom triggers for each table, or fashion a more generic trigger that works the same on lots of tables. Check out this article on audit triggers. This works by firing of triggers whenever a change is made, and the triggers log the changes. Remember that if you want to audit SELECT statements, you can't use triggers, you'll have to do that with in code/stored proc auditing. It's also worth remember that depending on your database, triggers may not fire in all circumstances. For example, most databases don't fire triggers during TRUNCATE statements. Check that your triggers get fired in any case that you need auditing.

Alternately, you could also take a look at using the service broker to do async auditing on a dedicated machine. This is more complex and takes a bit of configuring to set up.

Which ever way you do it you need to decide on the format the audit log will take. Normally you would save this log in your database, but you could just save it in a log file or whatever suits your requirements. You could use a single audit table that logs all changes, or you could have an audit table per main table being audited. For large scale implementations you could even consider putting the audit tables in a totally separate database. If your logging into a table, it's common to have a "change type" field which indicates if the audited change was an insert, update or delete style of change, along with the changed data, user who made the change and the date/time the change was made. Don't forget to include the old and new data for update style changes.

---

Related Topics