A quick overview of auditing in SQL Server 2012. We can audit actions at both the server level (like a failed logon attempt) and at the database level (like selects or modifications made to a table).

There are three components to SQL audit: Server Audit, Server Audit Specification, and Database Audit Specification.

Server Audit:
The Server Audit will specify where to store the audit data. We can choose the Windows Application log, Windows Security log, or a log file. We can have multiple specifications per SQL instance.

To create the audit:
CREATE SERVER AUDIT AuditName TO FILE (FILEPATH = ‘C:\Audit\’)
The audit is disabled by default, so we need to enable it:
ALTER SERVER AUDIT AuditName WITH (STATE = ON)

Server Audit Specification:
Here we audit server level events. We can specify multiple events in one statement. We’ll use the Server Audit set up above to store the results.

CREATE SERVER AUDIT SPECIFICATION ServerAudit
FOR SERVER AUDIT AuditName
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)

Query to return the available server audit events:
select name from sys.dm_audit_actions where class_desc = ‘SERVER’ and covering_action_name is null order by Name

Database Audit Specification:
Here we can track database level actions, like any selects or mods to a table. We’ll use the Server Audit set up in the first step to store the results. In this example, we’ll track any select or modifications made to [TableName] by the principal (user or role) we specify.

CREATE DATABASE AUDIT SPECIFICATION DatabaseAudit
FOR SERVER AUDIT AuditName
ADD (DELETE, INSERT, SELECT, UPDATE ON [TableName] BY [PrincipalName])
WITH (STATE = ON)

Query to return the available database audit events:
select name from sys.dm_audit_actions where class_desc = ‘DATABASE’ and covering_action_name is null order by Name

Our audit will record to a .sqlaudit file in the specified directory. The audit will append a code to each file created.
Since we are writing the audit records to our own log file, we’ll need to use the fn_get_audit_file function to return a table of the results. There can be multiple files, plus we need to account for the code appended to the file name, so we’ll use a wildcard in the name:
select * from sys.fn_get_audit_file(‘C:\Audit\AuditName*.sqlaudit’, null, null)

Audits must be set off before dropping.

ALTER SERVER AUDIT SPECIFICATION ServerAudit WITH (STATE = OFF)
ALTER SERVER AUDIT AuditName WITH (STATE = OFF)
DROP SERVER AUDIT SPECIFICATION ServerAudit
DROP SERVER AUDIT AuditName
GO

More information is available on MSDN at:
http://msdn.microsoft.com/en-us/library/cc280386.aspx