Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are you referring to the IASE STIG checklist? –  Shawn Melton Dec 30 '13 at 19:57
    
Did you want to use a server trace for auditing? Or were you asking to use SQL Server Audit? –  SQLRockstar Dec 30 '13 at 20:03

1 Answer 1

up vote 2 down vote accepted

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.