Auditing failed statements

New Contributor

Hi,

using sql server 2017 I fount the possibility to create audit logs for DML statements done by non-app users

 

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-dbo-GENERAL]
FOR SERVER AUDIT [Audit-Primary-dbo]
ADD (DELETE ON SCHEMA::xxx BY [dbo]),
ADD (INSERT ON SCHEMA::xxx BY [dbo]),
ADD (UPDATE ON SCHEMA::xxx BY [dbo]),
ADD (SELECT ON SCHEMA::xxx BY [dbo]),
ADD (DELETE ON SCHEMA::xxx BY [db_datawriter]),
ADD (INSERT ON SCHEMA::xxx BY [db_datawriter]),
...

 

 

I've been asked to audit also FAILED statements, like

  • permission denied for a select/insert/delete
  • statement causing integrity violations

Is there any way to put these in an audit log?

 

Also, as I will also have to audit schema changes, etc, is there any syntax for "ADD (ALL on schema:xxx by [dbo],[db_datawriter])" to a policy audit?

 

Thanks

3 Replies

Hi @albourg -- For the select/insert/delete events, SCHEMA_OBJECT_ACCESS_GROUP should capture these events.  For schema change audit, SCHEMA_OBJECT_CHANGE_GROUP.  For the "statements causing integrity violations", I assume(!?) you are referring to referential integrity violations which could be captured using the STATEMENT_ROLLBACK_GROUP.

 

I would recommend creating a filter on the server audit for schemas/objects that you do not want to audit (such as the sys schema), otherwise the audit will capture a lot of noise.  Lastly, be mindful of possible performance degradation and audit log management.  Take care.

@bake13I didn't find any STATEMENT_ROLLBACK_GROUP in the documentation of sql server 2016.

Do you have a link for a doc?

Hi @albourg  --  Your original post indicated SQL2017, so I'm unsure whether you are using SQL2016 or SQL2017.  Regardless, the audit action group is called TRANSACTION_GROUP.  Take care.

 

SQL Server Audit Action Groups and Actions - SQL Server | Microsoft Docs

 

bake13_0-1655984248713.png