ApexSQL Knowledgebase: Installing the Audit Tables on another Database

LINKS

General

Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script

Knowledgebase Home

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly. Click Here for more info.

FAQs

Quick answers to common questions. Click Here for more info.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.

Online Documentation

All of our help files for download or convenient viewing online. Click Here for more info.

Videos

Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools

Installing the Audit Tables on another Database

DESCRIPTION
This KB discusses how to store ApexSQL's Audit data on a separate database.

SOLUTION
To illustrate how, we call the database containing the Audit storage tables the “Audit Storage DB” while we call the database being audited the “Audited DB”. The first database's main purpose is reporting.

First, you need to make sure that the following Architecture structures are added/installed to the Audit Storage DB: DDL, Reporting (Standard and/or Aggregate), and Delete Architecture. For the Audited DB, at the very least, the DDL Architecture must be added. Triggers won’t be created without it. All these can be done via the Install Architecture dialog:

The Install Architecture dialog can be opened by choosing Install Auditing command from the Audit menu or by clicking the Auditing Toolbar,

. You may also click

from the Auditing Outlook Bar.

Installing Delete Architecture to the Audited DB is optional. Its main purpose is to automatically remove the Architecture and triggers, all of which can be done manually.

Next, the Trigger template of the Audited DB needs to be changed such that insertion statements point to the Audit Storage db. For example, [audit_db] in [audit_db].[dbo].[AUDIT_LOG_TRANSACTIONS] should be the Audit Storage database name. The template editor's Find/Replace feature can be used to do this.

Example
The default template contains insert statements like the one shown below:

    INSERT
    INTO {%Audit.Print DatabaseName%}.{%Audit.Print TransactionsTableName%}
    (
        TABLE_NAME,
        TABLE_SCHEMA,
        AUDIT_ACTION_ID,
        HOST_NAME,
        APP_NAME,
        MODIFIED_BY,
        MODIFIED_DATE,
        AFFECTED_ROWS,
        [DATABASE]
    )
    values(
        '{%Audit.Print Replace(objTable.Name, "'", "''")%}',
'{%Audit.Print Replace(objTable.Owner, "'", "''")%}',
        2,    --    ACTION ID For INSERT
        CASE
          WHEN LEN(HOST_NAME()) < 1 THEN ' '
          ELSE HOST_NAME()
        END,
        CASE
          WHEN LEN(APP_NAME()) < 1 THEN ' '
          ELSE APP_NAME()
        END,
        SUSER_SNAME(),
        GETDATE(),
        @ROWS_COUNT,
        '{%Audit.Print Audit.Database.Name%}'
    )

The name of the Audit Storage DB is defined by the DatabaseName variable of the trigger template:

 StartTriggerNameInsert = "tr_i_AUDIT_"
 StartTriggerNameUpdate = "tr_u_AUDIT_"
 StartTriggerNameDelete = "tr_d_AUDIT_"
 
 DatabaseName = "[" & Audit.Database.Name & "]"
 TransactionsTableName = "dbo.AUDIT_LOG_TRANSACTIONS"
 DataTableName = "dbo.AUDIT_LOG_DATA"

Change the value of this variable to define a custom name for the Audit Storage DB. For example,

DatabaseName = "MyStorageDatabase"

Make sure to give update rights to the audited tables' owners to allow for writing to the Audit Storage DB. The triggers are executed using the rights of the table owners.

Note: In SQL Server 2005, trigger executing rights can be defined for a user. For example:

' =============================================================
' =============================================================
' INSERT INSERT INSERT INSERT INSERT INSERT INSERT
' =============================================================
' =============================================================
caption = "Create Insert Trigger " & "[" & objTable.Owner & "].
 [" & StartTriggerNameInsert & objTable.Name & "]" & _
  " for Table " & TableFullName%}
IF OBJECT_ID('{%Audit.Print replace("[" & objTable.Owner & "].[" 
 & StartTriggerNameInsert & objTable.Name & "]", "'", 
  "''") %}','TR') IS NOT NULL
BEGIN
   DROP TRIGGER {%Audit.Print "[" & objTable.Owner & "].[" & 
 StartTriggerNameInsert & objTable.Name & "]" %}
   PRINT '{% Audit.Print "Trigger Dropped: " & StartTriggerNameInsert 
 & Replace(objTable.Name,"'","''")%}'
END
GO
-- {%Audit.Print caption%}
Print '{%Audit.Print Replace(caption, "'", "''")%}'
go
CREATE TRIGGER {%Audit.Print "[" & objTable.Owner & "].[" & 
 StartTriggerNameInsert & objTable.Name & "]" %}
ON {%Audit.Print TableFullName%}
WITH EXECUTE AS '{%Audit.Print Auditor%}'
FOR INSERT
NOT FOR REPLICATION
As

Above code shows the beginning of the INSERT trigger template. It displays the user whose rights will be applied for trigger executing context. The Auditor is a string variable that stores the user name. It should be defined somewhere at the top of the triggers template (for example, near the DatabaseName) as follows:

Auditor = "db_auditor"

For viewing the reports, connect to the Audit Storage DB. For creating the triggers, connect to the Audited DB.

Important: The UnDo procedure is not available in this approach.

AUTHOR
Denis Mack

LAST REVIEW DATE
10 August 2007

Labels: Apex SQL Audit

posted by Janice Lee on Friday, August 10, 2007

1 Comments:

At July 16, 2008 3:40 PM,

Chack said...

This is not clear at all, can you be more explicit? such as:

1) Open Apex SQL
2) Create a New Project File --> New Project
3) ...

After you said, click on intall auditing and make sure all databse level objects are checked (Delete Architecture)...

you skipped steps, it should be something like:

2) Close the window
3) Ctrl-R to open the template
4) Run the Template changing the Database Name as fig 1.1 (or something)

I got lost when I changed my template, but in my second database (Audit) there is no tables from Auditing!

How the program will create those tables in the second database?