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

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.