Figure 1 – Create a new Audit Specification

Continuing our learning series on auditing, today we create a new audit specification (as explained in last week’s post).  Remember, in order to create a new server audit specification, you must have created a new server audit.  This is a one-to-one relationship and in order to actually collect auditing information both objects are required.

Think of it like an order table that has to have an order lines table in order for a sales order to be made, if that helps your understanding.

1. In the security node of SQL Server Management Studio, right-click on Server Audit Specification and select New Server Audit Specification, as shown in Figure 1.
2. Change the name and use the drop down selections to select the type of server audits you would like to audit, as seen in Figure 2.  (MSDN explanation of groups and actions)
3. Right-click on the Server Audit Specification in the Security node and select enable to activate your new audit specification.

Figure 2 – Create Server Audit Specification

You can alternatively script this as well:

CREATE SERVER AUDIT SPECIFICATION [ChangingPermOwner]
FOR SERVER AUDIT [Audit-Test]
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)
GO

Enjoy and stay tuned!