• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Sql server Audit Table

I want to audit changes in all tables in the database, with all audit records going to the same table.
Something like
            Audit_ID int identity(1,1) PRIMARY KEY,
            OperationGUID uniqueidentifier, --action_id
            AppName     varchar(128) DEFAULT APP_NAME(), --application
            TableName varchar(128), --table_name
            KeyValue  varchar(128),
            Operation CHAR(1), --action_type
            DateChanged datetime DEFAULT GETDATE(), --action_datetime
            UserName varchar(128) DEFAULT SYSTEM_USER, --username
            ComputerName varchar(128) DEFAULT HOST_NAME(), --computer
            FieldName varchar(128), --column_name
            OldValue nvarchar(1024), --data_old
            NewValue nvarchar(1024) --data_new

INSERTs OldValue should be NULL,
for DELETEs NewValue should be NULL,
for UPDATEs I would need both old and new values (but only for the fields that changed).
if  someone already has the actual code ready then please pass it to me it will be greatly appreciated
  • 2
  • 2
3 Solutions
Have you looked at the Change Data Capture feature? It was designed to fulfil exactly your requirement:
sas77Author Commented:
dportas ,

Thanks you very much, but i need something very urgent so if  someone already has the actual code ready then please pass it to me it will be greatly appreciated
It takes about 30 seconds to enable CDC for a table and from that point the capture of audit data happens automatically. Example code here:


1) You need Enterprise Edition.

2) You need to understand and test the requirements re performance, logging, security, etc. The same applies to ANY form of audit for that matter. TANSTAAFL
sas77Author Commented:
That is the problem we are using The instance of SQL Server is the Standard Edition. Change data capture is only available in the Enterprise, Developer, and Enterprise Evaluation editions. So i need any alternate to do this .

Thank you
Here is a link to some code that you will have to customize a little and you would need to create a trigger for every table in your database. No the best way to go about auditing a database but this should get you started.  



Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now