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

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

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

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