Thursday, 28 February 2013

Creating a generic audit trigger

Creating a generic audit trigger with SQL 2005 CLR

If you are implementing an IT project of any significant size, then auditing is probably an important requirement of that project. Standard auditing usually includes tracking:
• insertions of new records,
• deletions of existing records,
• and modifications of fields in existing records.
A typical audit table looks like this:
Audit-Id Table Row-Id Operation Occurred-At Performed-By Field Old-Value New-Value
1 LARRY 2 UPDATE 2005-09-26 11:06:01.613 dbo Name George John
2 MOE 14 INSERT 2005-09-26 11:08:02.790 dbo NULL NULL NULL
3 CURLY 13 DELETE 2005-09-26 11:08:12.023 dbo NULL NULL NULL
For the sake of simplicity this example assumes a database in which all tables have an auto-increment primary key field, which makes auditing simpler (you can easily modify the example code below to handle more general cases). The columns of this audit table are as follows:
1. Audit-Id: the unique key of the audit table.
2. Table: the name of the table in which the audited event occurred.
3. Row-Id: the unique key of the row of the table in which the audited event occurred.
4. Operation: the type of operation (update, insert, delete).
5. Occurred-At: the date/time at which the event occurred
6. Performed-By: the database ID of the user who caused the event.
7. Field: the name of the specific field in which an update occurred (for updates only).
8. Old-Value: the value in the field before the update.
9. New-Value: the value in the field after the update.
The table looks simple but producing it isn't. To monitor every table in a database formerly required that you create a custom trigger for each table. Because Transact-SQL cannot inspect the metadata associated with the "inserted" and "deleted" pseudo-tables in a trigger, each trigger routine had to know the specific structure of its target table in advance. Thus despite the fact that auditing is a relatively mundane and repetitive chore, it was impossible to write a single generic routine that could function as a trigger for every table.
The new CLR integration in SQL Server 2005 changes all that because it places the full power of the CLR languages at your disposal. Without the restrictions of Transact-SQL, you can easily write a routine that can function as a trigger for every table in your database. The only per-table code you will need is a single declarative statement that associates your single CLR trigger routine with each table. That's one line of code per table rather than dozens of lines of custom code to be maintained with every table change. If you have a substantial project, that could mean a savings of dozens or perhaps even hundreds of man-hours per year.
Below is a generic CLR routine that populates an audit table having the structure shown in the example above. We will use it to produce an assembly called "AuditCommon". To produce the assembly:
  • use the September "Release Candidate" version of Visual Studio 2005 and SQL Server 2005 that was distributed at the 2005 Microsoft Professional Developers' Conference (this code was not tested on previous versions);
  • create a new VB / Database / SQL Server project called "AuditCommon" (this implicitly also creates a solution called "AuditCommon");
  • when prompted, associate the project with an empty database on some available SQL Server 2005 instance;
  • add a new "Trigger" item called "AuditCommon.vb";
  • copy the following code into the "AuditCommon.vb" module, completely overwriting the original template code that is automatically supplied in the file:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class Triggers
    ' This is the original template for Trigger metadata. Note that it is table-specific (i.e. it suggests
    ' that the trigger should apply to one table only).
    ' <Microsoft.SqlServer.Server.SqlTrigger(Name:="Trigger1", Target:="Table1", Event:="FOR UPDATE")> _

    ' This is our actual declaration. Note that it does not specify any particular table. We don't know
    ' if it is Microsoft's intention to allow table-agnostic trigger code, but this works and we hope
    ' that it keeps working.

    <Microsoft.SqlServer.Server.SqlTrigger(Name:="AuditCommon", Event:="FOR UPDATE, INSERT, DELETE")> _
    Public Shared Sub AuditCommon()


#If DEBUG Then
            EmitDebugMessage("Enter Trigger")
#End If

            ' Grab the already-open Connection to use as an argument
#If DEBUG Then
            EmitDebugMessage("Open Connection")
#End If
            Dim Context As SqlTriggerContext = SqlContext.TriggerContext()
            Dim Connection As New SqlConnection("context connection=true")

            ' Load the "inserted" table
#If DEBUG Then
            EmitDebugMessage("Load INSERTED")
#End If

            Dim TableLoader As New SqlDataAdapter("select * from inserted", Connection)
            Dim InsertedTable As New Data.DataTable

            ' Load the "deleted" table
#If DEBUG Then
            EmitDebugMessage("Load DELETED")
#End If
            TableLoader.SelectCommand.CommandText = "select * from deleted"
            Dim DeletedTable As New Data.DataTable

            ' Prepare the "audit" table for insertion
#If DEBUG Then
            EmitDebugMessage("Load AUDIT schema for insertion")
#End If
            Dim AuditAdapter As New SqlDataAdapter("select * from audit where 1 = 0", Connection)
            Dim AuditTable As New Data.DataTable
            AuditAdapter.FillSchema(AuditTable, SchemaType.Source)
            Dim AuditCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(AuditAdapter)

            ' Create DataRow objects corresponding to the trigger table rows.
#If DEBUG Then
            EmitDebugMessage("Create internal representations of trigger table rows")
#End If
            Dim TableName As String = ""

            Dim InsertedRow As Data.DataRow = Nothing
            If InsertedTable.Rows.Count > 0 Then
                InsertedRow = InsertedTable.Rows(0)
                TableName = DeriveTableNameFromKeyFieldName(InsertedTable.Columns(0).ColumnName)
            End If

            Dim DeletedRow As Data.DataRow = Nothing
            If DeletedTable.Rows.Count > 0 Then
                DeletedRow = DeletedTable.Rows(0)
                TableName = DeriveTableNameFromKeyFieldName(DeletedTable.Columns(0).ColumnName)
            End If

            ' get the current database user
            Dim CurrentUserCmd As New SqlCommand("select current_user", Connection)
            Dim CurrentUser As String = CurrentUserCmd.ExecuteScalar().ToString()

            ' Perform different audits based on the type of action.
            Select Case Context.TriggerAction

                Case TriggerAction.Update

                    ' Ensure that both INSERTED and DELETED are populated. If not, this is not a valid update.
                    If InsertedRow IsNot Nothing And DeletedRow IsNot Nothing Then

                        ' Walk through all the columns of the table.
                        For Each Column As Data.DataColumn In InsertedTable.Columns

                            ' ALTERNATIVE CODE to compare values and record only if they are different:
                            ' If Not DeletedRow.Item(Column.Ordinal).Equals(InsertedRow.Item(Column.Ordinal)) Then
                            ' This code records any attempt to update, whether the new value is different or not.
                            If Context.IsUpdatedColumn(Column.Ordinal) Then

                                ' DEBUG output indicating field change
#If DEBUG Then
                                EmitDebugMessage("Create UPDATE Audit: Column Name = " & Column.ColumnName & _
                                    ", Old Value = '" & DeletedRow.Item(Column.Ordinal).ToString & "'" & _
                                    ", New Value = '" & InsertedRow.Item(Column.Ordinal).ToString & "'")
#End If

                                ' Create audit record indicating field change
                                Dim AuditRow As Data.DataRow
                                AuditRow = AuditTable.NewRow()

                                With AuditRow
                                    ' populate fields common to all audit records
                                    Dim RowId As Int64 = CType(InsertedRow.Item(0), Int64)
                                    ' use "Inserted.TableName" when Microsoft fixes the CLR to supply it
                                    WriteCommonAuditData(AuditRow, TableName, RowId, CurrentUser, "UPDATE")
                                    ' write update-specific fields
                                    .Item("FieldName") = Column.ColumnName
                                    .Item("OldValue") = DeletedRow.Item(Column.Ordinal).ToString
                                    .Item("NewValue") = InsertedRow.Item(Column.Ordinal).ToString
                                End With

                                ' insert the new row into the audit table
                                AuditTable.Rows.InsertAt(AuditRow, 0)
                            End If

                    End If

                Case TriggerAction.Insert

                    ' If the INSERTED row is not populated, then this is not a valid insertion.
                    If InsertedRow IsNot Nothing Then

                        ' DEBUG output indicating row insertion
#If DEBUG Then
                        EmitDebugMessage("Create INSERT Audit: Row = '" & InsertedRow.Item(0).ToString & "'")
#End If

                        ' Create audit record indicating field change
                        Dim AuditRow As Data.DataRow
                        AuditRow = AuditTable.NewRow()

                        ' populate fields common to all audit records
                        Dim RowId As Int64 = CType(InsertedRow.Item(0), Int64)
                        ' use "Inserted.TableName" when Microsoft fixes the CLR to supply it
                        WriteCommonAuditData(AuditRow, TableName, RowId, CurrentUser, "INSERT")

                        ' insert the new row into the audit table
                        AuditTable.Rows.InsertAt(AuditRow, 0)

                    End If

                Case TriggerAction.Delete
                    ' If the DELETED row is not populated, then this is not a valid deletion.
                    If DeletedRow IsNot Nothing Then

                        ' DEBUG output indicating row insertion
#If DEBUG Then
                        EmitDebugMessage("Create DELETE Audit: Row = '" & DeletedRow.Item(0).ToString & "'")
#End If

                        ' Create audit record indicating field change
                        Dim AuditRow As Data.DataRow
                        AuditRow = AuditTable.NewRow()

                        ' populate fields common to all audit records
                        Dim RowId As Int64 = CType(DeletedRow.Item(0), Int64)
                        ' use "Inserted.TableName" when Microsoft fixes the CLR to supply it
                        WriteCommonAuditData(AuditRow, TableName, RowId, CurrentUser, "DELETE")

                        ' insert the new row into the audit table
                        AuditTable.Rows.InsertAt(AuditRow, 0)

                    End If

            End Select

            ' update the audit table

            ' finish
#If DEBUG Then
            EmitDebugMessage("Exit Trigger")
#End If

        Catch e As Exception

            ' Put exception handling code here if you want to connect this to your
            ' database-based error logging system. Without this Try/Catch block,
            ' any error in the trigger routine will stop the event that fired the trigger.
            ' This is early-stage development and we're not expecting any exceptions,
            ' so for the moment we just need to know about them if they occur.

        End Try

    End Sub
    ' Write data into the fields of an Audit table row that is common to all
    ' types of audit activities.
    Private Shared Sub WriteCommonAuditData(ByVal AuditRow As DataRow, ByVal TableName As String, ByVal RowId As Int64, ByVal CurrentUser As String, ByVal Operation As String)
        With AuditRow
            ' write common audit fields
            .Item("TableName") = TableName
            .Item("RowId") = RowId
            .Item("OccurredAt") = Now()
            .Item("PerformedBy") = CurrentUser
            .Item("Operation") = Operation
        End With
    End Sub
    ' SQL CLR does not deliver the proper table name from either InsertedTable.TableName
    ' or DeletedTable.TableName, so we must use a substitute based on our key naming
    ' convention. We assume that in each table, the KeyFieldName = TableName + "Id".
    ' Remove this routine and its uses as soon as we can get the table name from the CLR.
    Private Shared Function DeriveTableNameFromKeyFieldName(ByVal KeyFieldName As String) As String
        Return Left(KeyFieldName, Len(KeyFieldName) - 2).ToUpper() ' assumes KeyName = TableName & "Id"
    End Function
    ' Emit debug messages. This function gives us the option to turn off debugging
    ' messages by changing one value (here).
#If DEBUG Then
    Private Shared Sub EmitDebugMessage(ByVal Message As String)
    End Sub
#End If
End Class

Compile the above routine into an assembly. Now start up the SQL 2005 Management Studio and create a query window connected to your test database,. Begin by enabling CLR using the following Transact-SQL statements:
-- Configure the server for CLR operation.
sp_configure 'clr enabled', 1
For testing purposes, produce and populate a sample "ADDRESS" table to operate upon:
-- Create a test table.
if object_id('ADDRESS','U') is not null drop table ADDRESS
create table ADDRESS (
 AddressId bigint IDENTITY(1,1) NOT NULL,
 AddressLine varchar(50) NULL,
 City varchar(50) NULL,
 StateProvince varchar(50) NULL,
 PostalCode varchar(10) NULL,
 CountryCode varchar(10) NULL,

 constraint ADDRESS_PK primary key clustered 
 ( AddressId asc ) with (IGNORE_DUP_KEY = off) on [PRIMARY]

-- Populate the test table.
declare @Counter int
set @Counter = 0
declare @CounterString varchar(10)
while @Counter < 10
    set @Counter = @Counter + 1
    set @CounterString = cast(@Counter as varchar(10))
    declare @AddressLine varchar(50)
    set @AddressLine = 'Address Line ' + @CounterString
    declare @City varchar(50)
    set @City = 'City ' + @CounterString
    declare @StateProvince varchar(50)
    set @StateProvince = 'State/Province ' + @CounterString
    declare @PostalCode varchar(10)
    set @PostalCode = @CounterString
    declare @CountryCode varchar(10)
    set @CountryCode = 'C' + @CounterString

    insert into ADDRESS
Now create the audit table that the trigger routine will populate:
-- Create the audit table.
if object_id('AUDIT','U') is not null drop table AUDIT
create table AUDIT (
 -- audit key
 AuditId bigint identity(1,1) not null,

 -- required info for all auditing operations
 TableName varchar(50) not null,
 RowId bigint not null,
 Operation varchar(10) not null,
 OccurredAt datetime not null,
 PerformedBy varchar(50) not null,

 -- the following fields are used only when Operation = 'UPDATE' 
 FieldName varchar(50) null,
 OldValue varchar(1000) null,
 NewValue varchar(1000) null,

 constraint AUDIT_PK primary key clustered 
 ( AuditId asc ) with (IGNORE_DUP_KEY = off) on [PRIMARY]
) on [PRIMARY]
From this point on we are listing operations that you will need to redo every time you recompile the trigger. Use this code to load the server with the "AuditCommon" assembly containing the generic trigger routine (you must substitute your own project path here):
-- Create the assembly. When dropping the old one we must drop any
-- triggers created from it first.
declare @ProjectPath varchar(1000)
declare @SolutionFolder varchar (100)
set @SolutionFolder = 'AuditCommon'
declare @ProjectFolder varchar (100)
set @ProjectFolder = 'AuditCommon'
declare @AssemblyPath varchar(1000)
set @AssemblyPath = @ProjectPath + '\' + @SolutionFolder + '\' + @ProjectFolder + '\bin'
declare @AssemblyName varchar(100)
set @AssemblyName = 'AuditCommon'
declare @AssemblyObject varchar(1000)
set @AssemblyObject = @AssemblyPath + '\' + @AssemblyName + '.dll'
declare @AssemblyDebug varchar(1000)
set @AssemblyDebug = @AssemblyPath + '\' + @AssemblyName + '.pdb'

if object_id('Audit_Address','TA') is not null drop trigger Audit_ADDRESS
if exists(select name from sys.assemblies where name = @AssemblyName)
 drop assembly [AuditCommon]
create assembly [AuditCommon] from @AssemblyObject with permission_set = safe
begin try
    -- This adds debugging info; the file will not be present in your "release" version
    -- (as opposed to your "debug" version), so we don't want to fail if it's not there.
    alter assembly [AuditCommon] add file from @AssemblyDebug
end try
begin catch
end catch
Now associate the CLR trigger routine with the "ADDRESS" table. With the generic trigger, this is all the code you'll need to audit a table (you can stick this into your standard template for table creation):
-- Associate the generic CLR trigger with the ADDRESS table.
create trigger Audit_ADDRESS
on ADDRESS for insert, update, delete
as external name [AuditCommon].[AuditCommon.Triggers].AuditCommon
Now you are ready to test. Perform updates, insertions, and deletions and watch the AUDIT table fill up! Here are some sample tests:
-- Test "update"
update ADDRESS set City = 'New City 4' where AddressID = 4

-- Test "insert"
insert into ADDRESS
    'Inserted Address 1',
    'Inserted City 1',
    'Inserted StateProvince 1',

-- Test "delete"
delete from address where AddressID = 8
NOTE 1: In this example we use the SQL Management Studio to deploy the assembly, the trigger, and the test code. The built-in Visual Studio deployment and testing mechanism will NOT work with the generic trigger because that mechanism requires that you specify a particular table in the metadata of your trigger routine, and of course our purpose here is to avoid supplying such data.
NOTE 2: The CLR trigger routine relies on a field naming convention to extract the target table name because the "TableName" fields (i.e. DataTable.TableName in the code) of the "inserted" and "deleted" trigger tables are not being properly populated by SQL Server with the name of the target table (ironically these pseudo-tables contain essentially all the information you could want to know about the target table except its name). The convention involves using the table name as part of the name of the first (key) field. Obviously if your database does not employ such a convention, this trigger routine will be unable to provide the table name in your audit table. We have been informed by Microsoft that in a future release, the table name may be provided somewhere within the trigger context, but this capability will not be present in the initial release of SQL Server 2005.

An Introduction to SQL Server 2008 Audit

Excerpted from Brad’s Sure Guide to SQL Server 2008, which is available as a free eBook.
Previous versions of SQL Server have included a variety of built-in ways to audit activity inside SQL Server. These included:
Login Auditing: Only tracks user login successes and/or failures. Results are sent to the OS Application Log.
SQL Server Profiler (SQL Trace) Audit and Related Events: Profiler includes over 40 specific audit events, and over 130 additional events that can be traced. Results are stored in trace files.
DDL Triggers: DDL triggers can be added to a database to identify when any DDL event occurs.
C2 Audit Mode: This former auditing standard (now superseded by Common Criteria Compliance) uses SQL Trace to capture audit events, which are stored in trace files.
Common Criteria Compliance: A new international auditing standard which also uses SQL Trace to capture data, which are stored in trace files.
None of the above methods offer much granularity, most are not easy to administer, and with the exception of Login Auditing, they can add a large amount of overhead to running SQL Server, hurting its performance.
To help overcome these problems, SQL Server 2008 includes a feature called SQL Server Audit. SQL Server 2008 Enterprise Edition includes all of the features described in this article, which includes both the SQL Auditing Foundation and Fine Grained Auditing. SQL Server 2008 Standard Edition only provides the SQL Auditing Foundation.

Advantages of SQL Server Audit

SQL Server Audit includes these features and benefits:
The Ability to Audit at the Instance and Database Levels: When you configure an audit, you are given the ability to capture audit events at the instance-level or the database-level. In most cases, you will probably want to audit events at both levels.
The Ability to Audit Many Different Activities: SQL Server Audit includes many predefined activities that you can audit, including DML and DDL activity. In addition, you can even audit “audit activities”. In other words, the activities of DBAs, whose job it is to manage SQL Server Audit, can be monitored by outside parties, if so desired.
The Ability to Capture and View Audit Results: Audit results can be captured and stored in disk files, or in the operating system’s Event Logs. Data on disk can be imported into a database for further analysis and reporting. Data stored in Event Logs can be viewed using the Event Log Viewer.
High Granularity: SELECT, INSERT, UPDATE, DELETE, REFERENCES and EXECUTE statements can be captured for individual users at the object level, if desired.
Fast and Lightweight: SQL Server Audit uses SQL Server 2008′s Extended Events engine to capture audit data. This results in fast performance and minimal overhead as compared to using SQL Trace to capture activity.
Easy to Setup and Administer: SQL Server Audit can be setup and managed using either SSMS (SQL Server Management Studio) or Transact-SQL.

Limitations of SQL Server Audit

While SQL Server Audit includes many nice features, it also has some drawbacks you need to consider:
While SQL Server Audit takes up less physical resources than SQL Trace-based auditing options, it still uses SQL Server resources, and the more detailed your auditing, the more resources that are used. Because of this, it may not be practical to use SQL Server Audit on very busy OLTP servers, especially if they are already experiencing hardware bottlenecks.
SQL Server Audit is instance-based. In other words, there is no easy way to manage SQL Server Audit on all the SQL Server instances in your organization from a centralized location, unless you create your own method using scripts.
Audit data is stored either in a file, or as part of the operating system’s event logs. If you want to be able to analyze and report on this data, you will have to manually import it into your own database. In addition, DBAs will have to manually archive or delete old audit data.
There is no built-in reporting, other than looking at the events in the Log Viewer, assuming that is where you store the audit data. For effective reporting, you will have to create your own reports, most likely using SQL Server Reporting Services.

How SQL Server Audit Works

When you first begin using SQL Server Audit, you may find it somewhat unintuitive and a little confusing. In this section, I want to start with a high-level overview of how it works. In the following section, I will provide a short demonstration of it in action, so you can better see how it all fits together. The flow chart shown in Figure 1 should provide a broad overview of what’s involved in setting up auditing:
Figure 1: A flow chart showing how to create a new SQL Server Audit
SQL Server Audit allows you to create many different audits, covering most every activity that occurs inside SQL Server.
The first step when creating a new audit is to create a SQL Server Audit object. When you create a new SQL Server Audit object, you assign it a name, select from several configuration options, and you designate a target. A target is the location where the audit data will be stored. A target can be a file on disk, the Applications Event Log, or the Security Event Log. Once you have completed these steps, the new SQL Server Audit object is saved.
The second step is to create what is called an Audit Specification. SQL Server Audit offers two different types of Audit Specifications:
1. Server Audit Specifications - used when you want to audit an activity that occurs at the SQL Server instance level, such as auditing login and logout activity.
2. Database Audit Specifications - used when you want to audit an activity within a database, such as who is SELECTing data from a particular table.
Server and Database Audit Specifications are created differently so you need to decide which type you need up-front. When creating either type of Audit Specification, you first assign it a name, and then you must associate the Audit Specification with the SQL Server Audit object created in the first step. The rule is that a SQL Server Audit object can only be associated with one Audit Specification. In other words, you can’t reuse SQL Server Audit objects when you create Audit Specifications. And the last step to creating a Server Audit Specification is to assign it an Audit Action Type. An Audit Action Type is a predefined activity that occurs in SQL Server that can be audited.
When creating a Database Audit Specification, you assign it a name; then you associate the Audit Specification with a SQL Server Audit object; and specify an Audit Action Type, just as you do with a Server Audit Specification. However, in addition, you must also specify an Object Class (database, object, or schema), the name of an object to audit, and the security principal (the user account) that you want to audit.
Once the Audit Specification is completed, you must then enable both the SQL Server Audit Object and its associated Audit Specification. At this point, audit events will begin to be collected in the designated target.
And last, to view the audit data, you have several choices. If you store audit data in a file, you can import it into a database for viewing and reporting. If you store it in one of the two Event Logs, you can view it using the Event Log Reader.
I have left out a lot of details, but the above illustration and explanation should give you a fair understanding of how the overall process works.

A Simple Auditing Example

SQL Server Audit can be configured and managed using either SQL Server Management Studio (SSMS) or Transact-SQL commands. In this simple demonstration, we will use SSMS to create a simple audit because it is easier to understand for DBAs new to SQL Server Audit.
Creating an audit, and reviewing audit results using SSMS, is a four-step process, as outlined in the previous section:
1. Creating the Audit object
2. Creating a Server or Database Audit Specification
3. Starting the Audit
4. Reviewing Audit Events
In the following example, we want to find out who is looking at the HumanResources.EmployeePayHistory table in the AdventureWorks database. In other words, we want an audit trail of everybody who runs a SELECT statement against this table. Obviously, in the real world, your audit would be more comprehensive, but my goal here is only to provide a simple yet illustrative demonstration of how auditing works.

Creating the Audit Object

The first step is to create a new audit object. To create a new audit object using SSMS, go to the SQL Server instance you want to audit, open up “Security,” and you will see the “Audits” folder, as shown in Figure 2:
Figure 2: Choose “New Audit” to create an audit from within SSMS.
Right-click on the “Audits” folder and select “New Audit,” and the “Create Audit” dialog box appears, a shown in Figure 3:
Figure 3: To create an audit, you have to assign it a name and specify where the audit data will reside.
The first thing you need to do is to decide if you want to use the name that is automatically generated for you as the audit object name, or to assign your own name. Since numbers don’t mean much to me, I assigned it my own name.
Next, you have to provide a “Queue Delay” number. This refers to the amount of time after an audit event has occurred before it is forced to be processed and written to the log. The default value is 1000 milliseconds, or 1 second. While I am going to accept the default for this demo, you might want to consider increasing this value if you have a very busy server.
The next option on the screen is called “Shut down server on audit log failure”. If you select this option, and later SQL Server is restarted, and for whatever reason the audit data can’t be logged, then SQL Server will not start, unless you manually start it at the command line using a special parameter. This option should only be used in environments where very tight auditing standards are followed and you have 24/7 staff available to deal with the problem, should it occur.
Next, beside “Audit,” in the dialog box, there is a drop-down box with “File” selected by default. This option is used to tell SQL Server where you want the audit logs to be stored.
Figure 4: Three are three options where you can store audit data.
SQL Server Audit allows you to store audit data in a file, in the Security Log, or the Application Log. If you choose “File”, then you must also specify the location of the file, along with additional information, such as how much data it can collect, and so on. If you choose Security Log or Application Log, then the audit results are stored in these Windows Operating System Event Logs. I am going to choose “Application Log”. Once this is done, the dialog box should look as shown in Figure 5:
Figure 5: Once all the data has been provided, click “OK” to create the audit.
Now that the audit has been configured, click on “OK” to save it. It should then appear in the SSMS Object Browser, as shown in Figure 6:

Figure 6: Notice the red arrow next to the newly created audit.
The red arrow next to the audit object means that it is not currently enabled. That’s OK for now, we can enable it later.

Creating a Server or Database Audit Specification

Now that we have created the audit, we need to create the matching audit specification. If we wanted to do an instance-wide audit, we would create a server audit specification. But for this example, where the goal is to audit the SELECT activity on a single table in a single database, a database audit specification is created.
To create a database audit specification using SSMS, open up the database to be audited, then open up the security folder under it. Next, right-click on “Database Audit Specifications” and select “New Database Audit Specification”, as shown in Figure 7:
Figure 7: To create a database audit specification, you must do so from within the database you want to audit.
The “Create Database Audit Specification” dialog box appears, as shown in Figure 8:
Figure 8: The “Create Database Audit Specification” dialog box has many options to complete.
You can either choose to accept the default name assigned to this database specification, or you can enter your own. Next, select the appropriate audit object from the Audit dropdown box, as shown in Figure 9:
Figure 9: The “Create Database Audit Specification” dialog box has many options to complete.
In this case there is only one audit object, the “EmployeePayHistory”, as this is a newly installed SQL Server and doesn’t have any other audit objects on it.
Next, you must specify the kind of audit activity you want to capture by selecting from the “Audit Action Type” drop-down box, as shown in Figure 10:
Figure 10: You can select from many pre-defined audit actions.
For this example, I want to choose the “SELECT” “Audit Action Type,” as the goal is to record all SELECT activity for the payroll table. Of course, you can choose any audit action type you want, but you can only choose from those that are listed. You can’t create your own.
Now that the audit action type has been chosen, the “Object Class” must be chosen – see Figure 11:
Figure 11: In this case, you can choose from three object classes.
The object class allows us to narrow down the scope of what we want to audit. For this example, because we want to monitor activity on a table, “Object” is selected.
The next step is to specify the object, or the table name, that is to be audited. To do this, click on the browse button under “Object Name,” and the “Select Objects” dialog box appears, as shown in Figure 12:
Figure 12: The “Select Objects” dialog box allows you to select which object to audit.
Having clicked on the “Browse” button, the list of available objects will appear, as shown in Figure 13:
Figure 13: Select the object to be audited from this list.
Browse through the “Browse for Object” dialog box until you find the object or objects you want to audit, then select them. Above, I have selected a single table: HumanResources.EmployeePayHistory.
Once the objects have been selected, click “OK,” and the “Select Object” dialog box reappears, as shown in Figure 14:
Figure 14: The audited object has been selected.
Now that the object to be audited has been selected, click “OK,” and you are returned to the original “Create Database Audit Specification” dialog box, as shown in Figure 15:
Figure 15: We now see all of our actions up to this point.
There is one last step, and that is to specify what security principals (user accounts) that we want to monitor. To do this, click on the browse button under “Principal Name,” and another “Select Object” dialog box appears.
I am going to spare you seeing this screen again, and skip immediately to the “Browse for Object” dialog box, where you can see what principals you can choose from, as shown in Figure 16:
Figure 16: Select the principal you want to audit.
In this case, public is chosen, because the goal of this audit is to identify anyone who runs a SELECT against the payroll table. Optionally, you can select on specific users or roles. Click on “OK” for this dialog box, then click on “OK” for the “Select Objects” dialog box, and we reach the final screen, seen on Figure 17: 
Figure 17: We are finally done creating the database audit specification.
Since we are only interested in auditing this one table for a single action, we will stop now. If you wanted to, you could continue to add addition actions and objects to this audit specification. Click on “OK,” and the database Audit Specification will be saved, and you can view it in object explorer, as shown in Figure 18:
Figure 18: Notice the red arrow next to the specification, which tells us that it is turned off.
Once the new database audit specification has been created, it has a red arrow next to it, indicating that it is turned off. We will turn it on in the next step.

Starting the Audit

I have saved the steps of starting the audit till last because I wanted to show you that when you create an audit object, and a database audit specification, that they are turned off by default, and both must be turned on before audit data is collected.
First, turn on the audit object, which in our case is called “EmployeePayHistory,” then turn on the database audit specification, which in our case is called “EmployeePayHistoryAudit.” To turn these on, right-click on them, one at a time, and select “Enable Audit.” Auditing has now begun. At this point, any SELECT statements run against the HumanResources.EmployeePayHistory table are recorded and stored in the Application Events log file.

Reviewing Audit Events

Now, let’s see what all this work has done for us. To find out, open up the “Application Log File Viewer” and take a look. In this example, you’ll see something similar to Figure 19: 
Figure 19: When you click on an audit event, this is the detail information you see.
Because the log is large and hard to easily fit on a screen, I have scrolled to one of the many events in the Application Log (there a lot more you can’t see) and clicked on it. As you can see in figure 19, the details of the event provide a lot of information about a SELECT statement that ran against the audited table. Because of the potentially large quantity of information you can get back when using SQL Server Audit, I would suggest you store audit data to file, import this data into a SQL Server database, and then use Transact-SQL or Reporting Services to analyze the data. Using the Event Viewer to review audit data, as I have done here, is not very efficient.


While this seemed like a lot to cover, it is just small sample of how the SQL Server Audit feature of SQL Server 2008 works. SQL Server Audit is a powerful new tool that allows DBAs to collect almost any activity that occurs within our servers.
Overall, I would suggest that if you have been looking for a SQL Server auditing system, and have been considering purchasing a third-party auditing application, or creating your own, you will want to first carefully evaluate SQL Server Audit to see if it can meet your SQL Server auditing needs.