Skip to content

How to enable or disable MS SQL Audit for MSSQL instance

To enable Audit on a MSSQL Instance, follow the steps:

  1. Login to the instance where to Enable Audit Alt text

  2. Open and execute the below scripts in this exact order

  3. Create AND Enable Audit-CQ.sql

USE [master]
GO
CREATE SERVER AUDIT [Audit-CQ]
TO APPLICATION_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'f12b8f4e-f428-43e4-9fe4-da481f07eca1'
)
ALTER SERVER AUDIT [Audit-CQ] WITH (STATE = ON)
GO
  • Create AND Enable ServerAuditSpecification-Changes-CQ.sql
USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Changes-CQ]
FOR SERVER AUDIT [Audit-CQ]
ADD (AUDIT_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

  • Create AND Enable Audit-Permissions-Changes-CQ
USE [master]
GO
CREATE SERVER AUDIT [Audit-Permissions-Changes-CQ]
TO APPLICATION_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '275cc430-362d-44eb-8e84-5f82e34b90b9'
)
ALTER SERVER AUDIT [Audit-Permissions-Changes-CQ] WITH (STATE = ON)
GO
  • Create AND Enable Audit-ServerAuditSpecification-Permissions-Changes-CQ
 USE [master]
 GO
 CREATE SERVER AUDIT SPECIFICATION [Permissions-Changes-CQ]
 FOR SERVER AUDIT [Audit-Permissions-Changes-CQ]
 ADD (DATABASE_PERMISSION_CHANGE_GROUP),
 ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
 ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
 ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP)
 WITH (STATE = ON)
 GO

To disable Audit on a MSSQL Instance, follow the steps:

  1. Login to the instance where to Disable Audit

Alt text

  1. Open and execute the below scripts in this exact order

  2. Drop AND Disable Audit-CQ.sql

USE [master]
GO
ALTER SERVER AUDIT [Audit-CQ] WITH (STATE = OFF)
GO
DROP SERVER AUDIT [Audit-CQ]
GO
  • Drop AND Disable ServerAuditSpecification-Changes-CQ.sql
USE [master]
GO
ALTER SERVER AUDIT SPECIFICATION ServerAuditSpecification-Changes-CQ]
WITH (STATE = OFF)
GO
DROP SERVER AUDIT SPECIFICATION ServerAuditSpecification-Changes-CQ]
GO
  • Drop AND Disable Audit-Permissions-Changes-CQ.sql
USE [master]
GO
ALTER SERVER AUDIT [Audit-Permissions-Changes-CQ] WITH (STATE = OFF)
GO
DROP SERVER AUDIT [Audit-Permissions-Changes-CQ]
GO
  • Drop AND Disable Audit-ServerAuditSpecification-Permissions-Changes-CQ.sql
USE [master]
GO
ALTER SERVER AUDIT SPECIFICATION [Permissions-Changes-CQ]
WITH (STATE = OFF)
GO
DROP SERVER AUDIT SPECIFICATION [Permissions-Changes-CQ]
GO