x

How to implement audit trail for security, DDL events and Metadata in sql server

I need to implement metadata for millions of record changes in sql server.what is the best way to implement the metadata.I need even to store the old and new values, even the object type.

Thanks, Sally

more ▼

asked Oct 29, 2009 at 05:38 AM in Default

avatar image

savibp
52 6 7 8

I see you have edited your question. Was my answer any help?

Nov 03, 2009 at 10:32 AM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

"Millions of record changes" on DDL sounds a bit worrying?

It doesn't look like you mean DML, but this is what I do for DML:

I use a Trigger which stores the "Before" version of the record in a separate "Archive" table. I have seen other people store the "new" version in the archive table instead.

My view is that storing the New version means the archive table has a complete set of data, but it is duplicating the record currently in the Main table, which seems to me to be a waste of space - it immeditely doubles the size of the database before you have even changed any records :)

Here's an example of my Trigger:

CREATE TRIGGER dbo.MyTrigger ON dbo.MyTable AFTER INSERT, UPDATE, DELETE AS SET NOCOUNT ON SET XACT_ABORT ON SET ARITHABORT ON

 INSERT dbo.MyAuditTable            
 SELECT    [AuditType] = CASE WHEN I.MyPK IS NULL THEN 'D' ELSE 'U' END,            
     [AuditDate] = GetDate(),            
     D.*            
 FROM    deleted D            
     LEFT OUTER JOIN inserted I            
          ON I.MyPK = D.MyPK            

GO

For DDL you can do something similar in SQL2005 onwards along the lines of:

First create a table to hold the audit data:

CREATE TABLE MyDDLAudit            
(            
    EventID int identity NOT NULL,            
-- ... You probably also want a Date/Time column ...            
    EventData xml            
)            
GO            

Then create a DDL trigger

USE MyDatabase            
GO            
CREATE TRIGGER MyDDLTrigger            
    ON DATABASE            
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE            
AS            
    INSERT INTO MyDDLAudit            
    (            
        [EventData]            
    )             
    VALUES            
    (            
        eventdata()            
    )            
GO            

or this example for SQL Server Books Online:

USE AdventureWorks;            
GO            
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100),             
    Event nvarchar(100), TSQL nvarchar(2000));            
GO            
CREATE TRIGGER log             
ON DATABASE             
FOR DDL_DATABASE_LEVEL_EVENTS             
AS            
DECLARE @data XML            
SET @data = EVENTDATA()            
INSERT ddl_log             
   (PostTime, DB_User, Event, TSQL)             
   VALUES             
   (GETDATE(),             
   CONVERT(nvarchar(100), CURRENT_USER),             
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),             
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;            
GO            
--Test the trigger.            
CREATE TABLE TestTable (a int)            
DROP TABLE TestTable ;            
GO            
SELECT * FROM ddl_log ;            
GO            
--Drop the trigger.            
DROP TRIGGER log            
ON DATABASE            
GO            
--Drop table ddl_log.            
DROP TABLE ddl_log            
GO            
more ▼

answered Oct 29, 2009 at 09:59 AM

avatar image

Kristen ♦
2.2k 7 11 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2170

asked: Oct 29, 2009 at 05:38 AM

Seen: 1596 times

Last Updated: Nov 02, 2009 at 03:07 AM

Copyright 2017 Redgate Software. Privacy Policy