SQL Server Auditing: A Learning Series Part Five

newsauditspec

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.
servauditspec

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!

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 22, 2013, in Auditing and tagged , . Bookmark the permalink. 2 Comments.

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: