SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Audit: Getting Started

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

AuditSS

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.


Adam Kreul | Blog

I am currently a Senior DBA for Integrys Energy Services in Green Bay, WI. I’ve been a DBA since 2008 and have worked primarily with SQL Server versions 2005 and above supporting databases as large as 6TB.

Although my title says “DBA”, I also play the role of SAN administrator and data warehouse developer. I love learning and applying new technology and have a passion for making the environment I support as fast as possible. My blog is syndicated from www.adamkreul.com and you can follow me on Twitter at @AdamKreul.

Comments

Leave a comment on the original post [www.adamkreul.com, opens in a new window]

Loading comments...