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 Auditing Feature


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 instances of SQL has become more of a concern these days. SQL 2008 introduced a new feature named SQL Server Audit. In this tip I will cover how to setup auditing.

Solution

Enabling this feature can be done in just a few simple steps.

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

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. You will do that in the next step, first by right-clicking on the ‘Server Audit Specifications' as follows:

new server

Note that here we are creating an audit for the server, or instance level. In order to create a database audit you would navigate in SSMS to the database you want to audit and expand the security folder as follows:

adventure works

You can right-click on the Database Audit Specifications folder to create a new database audit. Each type of audit specification has a distinct set of options to select from. For this example we will continue with a database specification:

database specification

Select the server audit by clicking in the dropdown window. Then select an audit action type. In the example above I have selected a handful of action types and associated object classes, object names, and principal names. Click OK and the audit specification is created.

At this point we have created a server audit and a database audit that is associated with the server audit. Neither of these audits are enabled. You can enable them by right-clicking on each and selecting 'enable'.

Once enabled, SQL Server Audit is now collecting information. You can review the details by right-clicking on the server audit and selecting 'View Audit Logs'.

view audit logs

And the viewer should resemble the following:

bacon
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, May 04, 2012 - 3:03:36 AM - Ahmad Back To Top

Can i skipp all other old auditing techniques (Physical copy tables/Different Audit columns in each tables etc) to utilize the currnet/modern type of auditing?

Please comment!!!

Thank you


Learn more about SQL Server tools