Audit vs trace?

chrisrdba 471 Reputation points
2023-08-02T15:11:24.6066667+00:00

Greetings. I've been playing with Audits lately. My goal is to trace non application logins while they query table with PII data. All of our app logins follow a naming convention, so just writing a query in TSQL Im able to find the users I want to track by filtering out app logins with a NOT IN statement.

The problem is that I've got 150 tables with PII data, and about 20 users that would need to be audited. Audits dont work with NOT IN statements. Instead you need to specify which users to monitor. So each user to be audited would need to be listed as a Principal Name for all 150 tables. Doable, but not ideal as it would need to be manually maintained over time.

That said I look backwards in time to a server side trace. I could easily filter out which users to ignore, and filter in which tables I want to pay attention to.

Before I pull this trigger am I missing something about audits that makes them better than an old school trace for a mission like this? I cant be the first person to run in to this.

Thanks!

SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator
    2023-08-02T21:27:33.28+00:00

    I don't use Audit a whole lot, but as I read the syntax graph, I think you can say

    WHERE server_principal_name <> 'that_account' AND
          server_principal_name <> 'that_other_account' AND 
          ...
    

    Certainly more boring and verbose than NOT IN, but it seems that it should work.


0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.