Disable Audit Log for All MS SQL Server Audit using T-SQL
Disable Audit Log for single MS SQL Server Audit using T-SQL
For particular MS SQL Server Audit with sql audit name "Audit-SampleSQLServerAuditFile", you can disable audit log by executing a simple ALTER SERVER AUDIT t-sql command as shown in the below t-sql syntax :
ALTER SERVER AUDIT [Audit-SampleSQLServerAuditFile] WITH (STATE = OFF)
Or you can use the Microsoft SQL Server 2008 SQL Server Management Studio in order to disable audit log for a specific sql audit trail visually by the help of graphical user interface GUI.
Disable All SQL Audit Log for MS SQL Server 2008 using T-SQL
If you want to stop all MS SQL Server Audit log, you can imagine a database administrator can not disable each sql audit by right click on every SQL Server Audit and execute the Disable Audit menu command.
Using T-SQL ALTER SERVER AUDIT command and executing ALTER SERVER AUDIT for every SQL Server Audit log is easier and handy.
But where can we find all SQL Server Audit log entries for a MS SQL Server 2008 database instance.
All SQL Server audits created on a MS SQL Server 2008 database can be viewed by running a select query on sys.server_audits system view.
So for disabling all audits, the t-sql cursor code can be written as shown in the below sql cursor sample script :
DECLARE @audit_name nvarchar(100)
DECLARE @sql nvarchar(max)
DECLARE audit_cursor CURSOR FAST_FORWARD FOR
SELECT name
FROM sys.server_audits
WHERE is_state_enabled = 1
OPEN audit_cursor
FETCH NEXT FROM audit_cursor INTO @audit_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'ALTER SERVER AUDIT [' + @audit_name + '] WITH (STATE = OFF)'
EXEC sp_executesql @sql
FETCH NEXT FROM audit_cursor INTO @audit_name
END
CLOSE audit_cursor
DEALLOCATE audit_cursor