home articles news code blog links search

			Server and Database Auditing in SQL 2008 Overview Author Jasper Smith Hits 2017 Create Date 25-06-2008 Last Updated 25-06-2008 Versions SQL2008     Overview In SQL Server 2008 Enterprise Edition, Instance and Database level audit is now a built in function of the Database Engine with its own set of instance and database level objects, Server Audit and Server Audit Specification at the instance level and Database Audit Specification at the Database level (on a side note I'm not sure why they used the prefix Server since these are Instance level objects) These new objects also have their own DDL commands (CREATE, ALTER, DROP) which we will examine in the later articles. The generation of audit events is extremely lightweight compared to previously available mechanisms and is based on the new Extended Events infrastructure which is designed to have an extremely low overhead even for large numbers of events. It also allows much finer grained filtering of events. Whilst we had the ability to audit a large number of DDL actions in SQL Server 2005 using the Event Notification infrastructure (I have a sample tool that allows auditing of all schema changes across multiple instances and centralised storage and reporting available here) not all actions were auditable, it was not that straightforward to configure and there was no tool support within SSMS (SQL Server Management Studio). In SQL Server 2008, all events are auditable including those not available via Event Notifications and configuration is creatly simplified. As we'll see later on, there is also built in tool support for this in SSMS. The diagram below gives an overview of the various audit objects Server Audit objects define the properties of an audit (Queue Delay, Action on Audit Failure) as well as the output Target (File, Windows Application Log or Windows Security Log). You can create multiple Server Audits each of which defines its own Target. Server Audit Specification objects define the audit action groups that you want to audit at the Instance level and the Server Audit it belongs to. There can be a maximum of 1 Server Audit Specification per Server Audit. You can create multiple Server Audit Specifications as long as each one uses a separate Server Audit. Database Audit Specification objects define the individual audit actions or action groups that you want to audit at the Database level including any filters and the Server Audit it belongs to. There can be a maximum of 1 Database Audit Specification per Database per Server Audit. You can create multiple Database Audit Specifications for the same database but they need to belong to separate Server Audits. In the following series of articles I'll cover the three core objects in detail: Server Audit, Server Audit Specification and Database Audit Specification    Server and Database Auditing Part I - Server Audit    Server and Database Auditing Part II - Server Audit Specification    Server and Database Auditing Part II - Database Audit Specification   Print this page   Email this article   Add to Favourites

© Copyright sqldbatips Ltd 2008. Contact webmaster

home | news | articles | code | blog | links | search