Forum Discussion

albourg's avatar
albourg
Copper Contributor
Jun 22, 2022

Auditing failed statements

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

  • bake13's avatar
    bake13
    Icon for Microsoft rankMicrosoft
    Jun 22, 2022

    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.

Resources