T-SQL Tuesday #071 – SQL Server Audit

Invitation from Sebastian Meine.

For this month, I would like to invite you to write about Auditing. Auditing is certainly a security related topic, and with that fits right in with the August topic (Encryption).

But don’t write this up as yet another “boring” security topic. There are other use cases for auditing too. The built-in SQL Server Audit feature for example can be used to track down how many different applications are accessing a particular table.

The result of a SQL Server Audit

There are several approaches you can take with this topic. You could tell us a story:

  • Have you encountered a situation where auditing saved the day?
  • Where you able to stop an ongoing attack because auditing alerted you?
  • Have you encountered a situation, in which auditing would have been helpful, but was not set up?
  • Have you worked with the SQL Server Audit feature? What is particularly interesting to you about it?
  • Do you think that everybody should use some form of auditing? Let us know, why.
  • Do you think auditing is a waste of resources? We would like to hear more.
  • Are you forced to be compliant? Under what regulation? HIIPA, PCI, CCC? How did auditing help to get you compliant?

If stories are not your thing, let us know how you use auditing. Or, write about how to use a fascinating piece of SQL Server Audit.

  • What are the advantages and disadvantages of SQL Server Audit over other possible audit implementations, like triggers, traces, Extended Events or external tools like log file readers?
  • How can you use SQL Server Audit to see if a particular table or procedure is still in use?
  • What is the difference between a Server Audit Specification and a Database Audit Specification and when should you use which?
  • SQL Server Audit is based on Extended Events. What does it offer that XEs do not provide?

Finally, you could go totally meta:

  • How do you audit the audit? How do you make sure that the audit does not just get disabled by an adversary?
  • How do you monitor your audit log to make sure you get alerted when something irregular is happening?

I hope I was able to spark your interest. I can’t wait to see you (or at least your post) next week at the party.


T-SQL Tuesday #045 – Follow the Yellow Brick Road

Invitation and summary from Mickey Stuewe.

An audit trail is needed for various reasons. Some companies need it for compliance, others need it to find out who “accidently” did something stupid last week, and some specialized audit trails can tell you how the data has changed over time.

So, it is time to follow Dorothy and Toto down the yellow brick road and to share your experience with auditing data. If you are new to the T-SQL Tuesday blog party and need some ideas, here are a few:

  • How to implement SQL Server Audit which was introduced in SQL 2008.
  • Your favorite audit pattern.
  • Your worst experience with an implementation of a bad auditing pattern.