SQL Server has the ability to monitor both server and database level events via the SQL Server Audit feature.  Audited events can be written to the server application or security logs, or to a file where you specify the location (the most secure route).   SQL Server Audit is easy to set up, and requires at most three objects be created: an audit object, and a server and/or database audit specification object.

In this example, we’ll create a SQL Server Audit that monitors backup and restore activity for the instance (server level audit) and monitors the DDL statements on the AdventureWorks2008R2 database (database level audit).  Audit information will be stored in a file, which can be queried via Management Studio.

First, we’ll create the initial audit object.  Note that all of these steps can also be performed via the GUI, but scripting makes me feel more powerful.

USE master
GO
CREATE SERVER Audit KreulAudit
TO FILE (FILEPATH = 'C:\AdventureWorks2008R2_Database\Audit');
GO

Next, we need the server and database audit specifications.

USE master
GO
CREATE SERVER Audit SPECIFICATION KreulAudit_ServerSpec
FOR SERVER AUDIT KreulAudit
ADD(BACKUP_RESTORE_GROUP)
WITH (STATE = ON);
GO
USE AdventureWorks2008R2
GO
CREATE DATABASE AUDIT SPECIFICATION KreulAudit_DatabaseSpec
FOR SERVER AUDIT KreulAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE=ON);
GO

Lastly, we enable the audit.

USE master
GO
ALTER SERVER Audit KreulAudit WITH (STATE = ON);
GO

After running a backup and dropping a view, we should now see some activity in our audit file.  The fn_get_audit_file function allows us to read the file right in Management Studio.

SELECT event_time, [statement] FROM 
sys.fn_get_audit_file('C:\AdventureWorks2008R2_Database\Audit\*', null, null);
GO

The audit successfully captured both events.  Note that the first record returned is just the header record indicating an audit session was started.  This occurred when we enabled the audit.

There are many different audit action groups in addition to the examples above, and they’re listed here.  There are quite a few that you’ll see listed both in the server and database group, but the key to remember is that the server audits are at an instance level, and database audits are at a database level.

Follow @AdamKreul