SQL Server Auditing: A Learning Series Part Three

newaudit

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:

CREATE SERVER AUDIT [Test-Audit] TO SECURITY_LOG WITH (QUEUE_DELAY = 1000)

Enjoy and stay tuned.

Advertisements

About SQLGator

Florida Gator and Microsoft Data Platform MVP, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server DBA who dabbles with VMWare, 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: