question

savibp avatar image
savibp asked

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

sql-server-2008
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image Kristen ♦ commented ·
I see you have edited your question. Was my answer any help?
0 Likes 0 ·

1 Answer

·
Kristen avatar image
Kristen answered

"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            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.