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!