Sql-server – Setting Audit Events in SQL Server 2012

sql serversql-server-2012

We have to do information assurance remediation on our SQL Server 2012 and came across the following finding: Check to see that all required events are being audited. So I ran these queries:

select DISTINCT traceid from ::fn_trace_getinfo('0')

Select DISTINCT(eventid) FROM ::fn_trace_geteventinfo('1')

This returned about 34 different eventids that I assume are being used for audit purposes. Most of the items returned are NOT to be audited and should be replaced with others. However, I have no idea how to remove eventids and how to add new ones. For instance I am missing 14,15,112,113,114,118,128,129 and many others. So my question is how to I remove these audits and add news ones? A way to script it would be best or at least run it as a query.

Best Answer

This sample might help in order to script events. To capture the specific events, you need to create a Server audit specification first - The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor:

USE master;
GO
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'c:\audits\', MAXSIZE = 2 GB)
WITH (ON_FAILURE = CONTINUE);
GO
ALTER SERVER AUDIT ServerAudit
WITH (STATE = ON);

The next step is to create a Database audit specification at the database level. The audit group in this example is is SCHEMA_OBJECT_CHANGE_GROUP

USE [ACMEDB];
GO
CREATE DATABASE AUDIT SPECIFICATION schema_change
FOR SERVER AUDIT ServerAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

Finally, for example, query the previously created audit, by using the LIKE operator to narrow down captured entries to the ones related to enable/disable triggers:

SELECT
   event_time AS [Time],
   server_principal_name AS [User],
   object_name AS [Object name],
   Statement
  FROM sys.fn_get_audit_file('c:\audits\ServerAudit*', NULL, NULL)
WHERE
   database_name
   =
   'ACMEDB'
AND (
   Statement LIKE '%DISABLE%TRIGGER%'
OR Statement LIKE '%ENABLE%TRIGGER%')ORDER BY
                                      [Time] DESC;
Related Question