CREATE PROCEDURE dbo.GDPRAuditEnableDisable @AEnable BIT AS
SET NOCOUNT ON
/*
  Created by Kent Johansen. November 2017
  Enable/disable database audits.
*/

DECLARE @audit_name nvarchar(100)
DECLARE @sql nvarchar(max)

DECLARE audit_cursor CURSOR FAST_FORWARD FOR
  SELECT Name 
  FROM sys.database_audit_specifications WITH (NOLOCK)
  WHERE ((@AEnable = 1 AND is_state_enabled = 0) OR (@AEnable = 0 AND is_state_enabled = 1))
OPEN audit_cursor

FETCH NEXT FROM audit_cursor INTO @audit_name

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @AEnable = 1
    SET @sql = N'ALTER DATABASE AUDIT SPECIFICATION [' + @audit_name + '] WITH (STATE = ON)'
  ELSE
    SET @sql = N'ALTER DATABASE AUDIT SPECIFICATION [' + @audit_name + '] WITH (STATE = OFF)'
  EXEC sp_executesql @sql

  FETCH NEXT FROM audit_cursor INTO @audit_name
END

CLOSE audit_cursor
DEALLOCATE audit_cursor