By: Thomas LaRock | Updated: 2010-03-10 | Comments (1) | Related: > Auditing and Compliance
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.
You should see a screen as follows:
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.
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:
Next Steps
- Configure SQL Server 2008 Audit to use the AUDIT_CHANGE_GROUP by following the steps outlined above.
- Check out the following tips:
About the author
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
Article Last Updated: 2010-03-10
