How to enable or disable MS SQL Audit for MSSQL instance
Enable Audit on a MSSQL Instance
To enable Audit on a MSSQL Instance, follow the steps bellow:
1.Login to the instance where to Enable Audit
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
3.To configure login auditing, follow the steps bellow:
a.In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine with Object Explorer.
b.In Object Explorer, right-click the server name, and then click Properties.
c.On the Security page, under Login auditing, click the desired option and close the Server Properties page.
d.In Object Explorer, right-click the server name, and then click Restart.
Disable Audit on a MSSQL Instance
To disable Audit on a MSSQL Instance, follow the steps:
1.Login to the instance where to Disable Audit
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