How to Create Database Audit Specification for a column?

Question

I am creating SQL Server 2008 R2 Database audit Specification for tblPerson.SSN

I selected

Audit Action Type: Update
Object Class: Object
Object Name: tblPerson
Principal name: Public

It captures all changes to the table. How could I see just one column?

I could filter my column out

select statement,* from fn_get_audit_file ('q:\SQLBackup\*',null,null) 
where statement like '%SSN%'

If you want to audit changes to just one column, I suggest you use a trigger instead. — HLGEM, Commented Aug 17, 2012 at 14:10

Mr.Brownstone · Accepted Answer · 2012-08-17 09:15:54Z

I am not sure you can. However it is a bit confusing, MSDN says that it is possible:

However following the example that they give I get the following error:

Msg 33211, Level 15, State 1, Line 0 A list of subentities, such as columns, cannot be specified for entity-level audits.

The message above seems to be pretty definitive, I have tried their example on 2008, 2008 R2 and 2012 and I get the same message every time.

1 Answer 1