SQL Server 2008 Audit Change Group

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


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.


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:










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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, June 7, 2013 - 4:11:22 PM - Walger Back To Top (25345)

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.



get free sql tips
agree to terms