SQL Server 2012 – Auditing SELECT of a Single Column

auditsql serversql-server-2012

Is this possible, or is table-level the most granular access?

So far, Google search has shown nothing, and this screenshot does not mention column, but I thought I'd ask here.

SQL Server version is 2012 SP4-OD.

enter image description here

Best Answer

Not knowing your systems or the load on them, I cannot stress enough the need to test this suggested solution, very thoroughly. However, that said, you can do this, partly, through Extended Events. Here's a sample script:

    CREATE EVENT SESSION [ColumnAudit]
ON SERVER
    ADD EVENT sqlserver.sp_statement_completed
    (WHERE (
               [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'HamShackRadio')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%RadioName%')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%SELECT%')
           )
    ),
    ADD EVENT sqlserver.sql_statement_completed
    (WHERE (
               [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'HamShackRadio')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%RadioName%')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%SELECT%')
           )
    );

A few notes on this.

First, I would strongly recommend that you put good filtering in place. I've filtered first on the database name in order to only capture this from the one database. Second, I've ensured that I can filter the statement for the columnname ahead of anything else to be sure I'm only looking at those statement. Then I filter on SELECT.

Second, lest you balk at all to use Extended Events, remember that Audit is just using them behind the scenes, so this is a normal use of the functionality offered.

Third, and most importantly, test this. I've no idea how a wild card filter like this is likely to behave on your system. If you have another way to add additional filters before the wild card, say, on object_id or something, I would.

Finally, this won't work if someone types SELECT * FROM dbo.MyTable. I'm not sure how I'd tackle attempting to capture that. I guess you could filter for SELECT, the table name, and *. That would probably do it.

In case I haven't emphasized it enough, test this thoroughly before you implement it in production.

Related Question