Automations

Quick SQL Audit Solution

Here’s a quick audit solution using SQL Server Auditing. This will only work if you are using Enterprise Edition.

use master;
set nocount on
create server audit [General_SQL_Server_Audit_Set]
to file
(
filepath = '\MyShareMyFolder' -- you do not have to enter a file name. this is automatic based on the audit name.
,maxsize = 0 mb
,max_rollover_files = 2147483647
,reserve_disk_space = off
)
with ( queue_delay = 1000, on_failure = continue )
alter server audit [General_SQL_Server_Audit_Set] with (state = on)
go
waitfor delay '00:00:03'
create server audit specification [UserAccessAudit]
for server audit [General_SQL_Server_Audit_Set]
add (server_principal_change_group),
add (database_object_access_group)
with (state = 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 [General_SQL_Server_Audit_Set]' + char(10) +
'add (select, update, insert, 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)

Just remember to right-click the Audit file, and make sure it’s enabled.

Hope this is useful.

Advertisements

3 replies »

  1. use master;
    set nocount on

    declare @remove_db_audits varchar(max)
    set @remove_db_audits = ”
    select @remove_db_audits = @remove_db_audits +
    ‘use [‘ + name + ‘]; ‘ + char(10) +
    ‘DROP DATABASE AUDIT SPECIFICATION [‘ + name + ‘];’ + char(10) + char(10)
    from sys.databases where name not in (‘master’, ‘model’, ‘msdb’, ‘tempdb’)
    exec (@remove_db_audits)
    go

    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 [General_SQL_Server_Audit_Set]’ + char(10) +
    ‘add (update, insert, delete 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)
    go

  2. Thats not bad if you want to just capture insert, updates, deletes. Don’t forget that in order for the Database Audit Specification to be dropped you’ll need to first disable it. I’ve modified your code to include that.

    use master;
    set nocount on

    declare @remove_db_audits varchar(max)
    set @remove_db_audits = ”
    select @remove_db_audits = @remove_db_audits +
    ‘use [‘ + name + ‘]; ‘ + char(10) +
    ‘alter database audit specification [UserAccessAudit_’ + name + ‘]’ + char(10) +
    ‘with (state = off)’ + char(10) +
    ‘drop database audit specification [UserAccessAudit_’ + name + ‘];’ + char(10) + char(10)
    from sys.databases where name not in (‘master’, ‘model’, ‘msdb’, ‘tempdb’)
    exec (@remove_db_audits)
    go

    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 [General_SQL_Server_Audit_Set]’ + char(10) +
    ‘add (update, insert, delete 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)
    go

  3. Fyi; under the current code you really don’t need to right-click and ‘enable’. Should already be enabled. It’s only when you script it out through some automated SQL process that it might be disabled explicitly.

    Other than that; this is an excellent and quick way to get audits on the database server.

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