Forensics & Auditing

Create Database Audits For All User Databases

So you have your SQL Audit setup which is cool, but now you want to capture all the SELECT, INSERT, UPDATE, DELETES, EXECUTES, etc..

( By the way if you’re somewhat new at SQL Auditing ApexSQL has an excellent post about it here: http://solutioncenter.apexsql.com/sql-server-database-auditing-techniques/ )

You can use this little script to create a uniform audit capture across all the databases. If you want to see each individual audit configuration; all you need to do is change the last line to this: select (@create_db_audits) for xml path(”), type

use master;
set nocount on

declare @create_db_audits varchar(max)
set @create_db_audits = ''
select @create_db_audits = @create_db_audits + 'use [' + name + '];' + char(10) +
'create database audit specification [UserAccessAudit_' + name + ']' + char(10) + 'for server audit [MyServerAuditName]' + char(10) +
'add (select, insert, update, delete, execute, receive, references on database::' + name + ' by public)' + char(10) + 'with (state = on);' + char(10) + char(10)
from sys.databases where name not in ('master', 'model', 'msdb', 'tempdb')

exec (@create_db_audits) --for xml path(''), type
Advertisements

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