Sql server Audit Table

Posted on 2009-04-27
Last Modified: 2012-05-06
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
Question by:sas77
    LVL 22

    Assisted Solution

    Have you looked at the Change Data Capture feature? It was designed to fulfil exactly your requirement:

    Author Comment

    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
    LVL 22

    Assisted Solution

    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

    Author Comment

    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
    LVL 1

    Accepted Solution

    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

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now