How to enable or disable MS SQL Audit for MSSQL instance
To enable Audit on a MSSQL Instance, follow the steps:
-
Login to the instance where to Enable Audit
-
Open and execute the below scripts in this exact order
-
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:
- Login to the instance where to Disable Audit
-
Open and execute the below scripts in this exact order
-
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