Need an Audit? SQL Server 2008 is your friend

SQL Server 2008 has some great new features. One of them is the new auditing capability. Now we can audit at the server, database and table level according to specifications provided by the DBA. We could audit such things as successful and failed logins in previous releases but now we have much more granular capability. Let's take a look at the new functionality...

First thing to do is create a server audit object. This defines where the log entry will go. You have a choice of a file, the Windows Application Log or the Windows Security Log. You will need extra security privilege to log in the Windows Security Log. You can also define synchronous or asynchronous processing and what to do should the log entry fail to be recorded. In extremely secure environments this might require a shutdown of SQL Server since a hacker may be attempting to cover their tracks. Of course, in this case, the hacker has succeeded in producing a Denial of Service condition but that's better than potential loss of data. The SQL Slammer virus of 2003 was a DOS attack. Luckily nobody lost any data in that attack but that was a big wake-up call for Microsoft. Many security features since have helped us lock down our servers. Now we can supplement that with more sophisticated auditing.

Once the server audit object is created, you can define events via specification statements either at the server level or at the database level. An example of a server level specification is to track failed logins. An example of a Database level specification is to audit who is changing metadata within a database, or to track who is updating data within tables in a particular schema. You can even track particular users, if you wish.

I tested this out to audit a particular user updating a particular set of tables and logged the results in the application log. It worked well, although the actual data values were not recorded, the essential details of the update were logged correctly - what, when and who. Don't worry, I can always use the new Change Data Capture feature in 2008 should I need more detail on actual data values.

All this adds up to more precise control for auditing in SQL Server. Once again, Microsoft is giving us more capability that was once provided only by third party products. Of course, the third parties will now have to improve their products to justify the extra license costs.

This is all good news for you and me, the database administrator and consumer.              

 Cheers

Brian

Recent posts:

SQL Server 2008 Performance Benchmarks - a political position

Resource Governor 2008 – First look

Intellisense – how good is it?

• Microsoft
• SQL Server 2008