Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2008 Audit Change Group


By:   |   Read Comments (1)   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

Auditing your SQL Server instances has become more of a concern these days. SQL Server 2008 introduced a new feature named SQL Server Audit. Enabling this feature can be done in just a few simple steps, but so could disabling this feature. And when it comes to audits, many times you are asked to provide proof that the audit itself has not been tampered with.

Solution

SQL Server Audit offers the ability to "audit the audit", so to speak. There is an action group specific for audit-level actions. This group allows for you to effectively monitor any actions taken that affect existing audit specifications. You can monitor for changes made to either database audits or server audits.

The first step is to create a new audit by right-clicking on the Audit folder inside of the Security folder found in SSMS.

new audit

You should see a screen as follows:

create audit

You need to input a file path in order to proceed. You can also set parameters for the file size and reserve disk space for the files. However, you cannot reserve disk space if your file size is set to unlimited.

After you click OK, you will have created a server audit. At this point you have created an audit for the instance, but have not specified what to audit. Here is where you have to take a minute to think about what it is you are required to audit. Do you need to audit activities inside of a particular database? If so, then you will need to create a Database Audit Specification. You will do that by navigating inside of SQL Server Management Studio to the target database and expanding the Security folder. From there it is a right-click on the Database Audit Specifications folder.

If you need to audit activities against the instance then you need a Server Audit Specification, which is found in SSMS inside the Security folder for the instance you are connected to. In either case, in order to audit changes to a specification you need to add the AUDIT_CHANGE_GROUP to the desired specification.

database audit

The AUDIT_CHANGE_GROUP will raise an event whenever one of the following commands are issued:

CREATE SERVER AUDIT

ALTER SERVER AUDIT

DROP SERVER AUDIT

CREATE SERVER AUDIT SPECIFICATION

ALTER SERVER AUDIT SPECIFICATION

DROP SERVER AUDIT SPECIFICATION

CREATE DATABASE AUDIT SPECIFICATION

ALTER DATABASE AUDIT SPECIFICATION

DROP DATABASE AUDIT SPECIFICATION

This list could give the impression that inside of a database audit specification you would be altering changes made to the server audit, but that is not true. This list is simply providing details on all commands, not on how or where the commands are raised.

Server Audits themselves are always logged upon creation, alteration, and deletion. Server audit specifications that have the AUDIT_CHANGE_GROUP explicitly added will see those same events logged.

When you add the AUDIT_CHANGE_GROUP action group to a database audit specification it only raises an event for changes that were attempted to be made to that particular database audit specification. I say 'attempted', because in order to make changes to an audit specification you need to disable the specification first. If you disable the database audit specification, that gets logged. If you attempt to make a change while the database audit specification is enabled, it will fail, and your attempt will still be logged. The same applies to server audit specifications; disabling or attempting to alter server audit specifications will be logged if the AUDIT_CHANGE_GROUP is explicitly added.

And the log viewer should resemble the following:

log viewer
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, June 07, 2013 - 4:11:22 PM - Walger Back To Top

Hello, very good your contributions,

I have a question?

How I can set the time the audit event with the system time?

in the image of his example also appears unconfigured.

 

Thanks


Learn more about SQL Server tools