0

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

2
  • You'll probably want to audit all selects then, when reviewing the audit data, filter out anything that doesn't explicitly mention the column (and of course is not SELECT * - you can't rely on the column being mentioned to match the column being selected). You'll also want to be sure to factor in queries that infer data about the column without including it in the output (e.g. exploratory SELECT EmployeeID ... WHERE Salary BETWEEN x AND y;. Salary isn't in the output, but it doesn't have to be. Jun 8, 2020 at 22:30
  • Rather than (or in addition to) audit, you might consider restricting access to that column (unless you have sysadmin people you need to hide it from). Jun 8, 2020 at 22:32

1 Answer 1

1

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.

3
  • In your example, is RadioName the column name?
    – RonJohn
    Jun 9, 2020 at 13:58
  • I changed [statement] to [sql_text] and combined the strings into %SELECT%RadioName%. It now works like a charm.
    – RonJohn
    Jun 9, 2020 at 20:00
  • Yeah, RadioName was a column name. As to using statement vs. sql_text, it depends on the event. Jun 10, 2020 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.