SQL Server Auditing: A Learning Series Part Three


Figure 1 – New Audit

Continuing our learning series on Auditing, today we move on to setting up the server audit which will allow us to be able to setup auditing at the instance level.

  1. In SSMS, open the Security node and right-click on Audits and choose New Audit as shown in Figure 1.
  2. This will present us with a default Create Audit dialog box as shown in Figure 2.  Enter a name for your Audit being as descriptive as possible to make it easy for you to look back later and now what this is auditing.
  3. The Queue delay default is 1000 milliseconds.  This is the delay before the audit actions will be processed.
  4. There is a check box if you would like to shut down the server when the audit log fails.  In SQL Server 2012 you have the option to Continue, Shut down the server, or Fail the operation.  Figure 2 is from a SQL Server 2008 server.
  5. As you can see in Figure 2, the default setting for the destination is the file and if this is the setting you choose then you must enter a file path in addition to the other settings shown.  If you choose Security Log or Application Log then all of the file options will be unavailable.
  6. After this process is complete, you will see the audit named under Audits in the Security node.  From there you need to right-click on it and select Enable Audit.
Figure 2 - Create Audit Defaults

Figure 2 – Create Audit Defaults

As with anything in the GUI SSMS, this too can be scripted as an example:


Enjoy and stay tuned.


About SQLGator

Microsoft Data Platform MVP, Florida Gator, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server Business Intelligence Consultant, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on January 17, 2013, in Auditing, Security and tagged , . Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: