Skip to content

How to enable or disable MS SQL Audit for MSSQL instance

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

1) Login to the instance where to Enable Audit

Alt text

2) Open and execute the below scripts in this exact order:

A) 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

B) 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

C) Create AND Enable Audit-Permissions-Changes-CQ.sql

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

D) Create AND Enable Audit-ServerAuditSpecification-Permissions-Changes-CQ.sql

 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

2) Open and execute the below scripts in this exact order:

A) Drop AND Disable Audit-CQ.sql

USE [master]
GO
ALTER SERVER AUDIT [Audit-CQ] WITH (STATE = OFF)
GO
DROP SERVER AUDIT [Audit-CQ]
GO

B) 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

C) 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

D) 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