Sql-server – Hiding or masking sensitive information in the SQL Server audit log

auditsql serversql-server-2016

I'm looking into auditing of who are accessing the data in all tables in a database. We do have the additional requirement that we in the log can't have any information that can be used to identify a person.

The first part can easily done using SQL Server audit to log all select, insert and delete statements for a specified database. It is the second part that I have problem with. The whole SQL statement including any personal information in e.g the where clause will be stored in the 'statement' column. This meaning that whoever that have access to the audit log will be able to see this information and identify the person in question. We do not need the information that are stored in the 'statement' column. The information stored in 'database_name', 'schema_name' and 'object_name' columns are enough for us.

My question is are there any way to prevent SQL Server audit to store the SQL statement at all or at least in clear text in the 'statement'column?

Best Answer

I got the following answer from Emily Fei at msdn Forums

No, SQL Server Audit can't be able to choose the event field name. The Audit log file will record the full event field name.

Seems the only way to achieve something like this is to restrict the access to the raw audit log and then publish a subset of the log for more general use.