Database objects DML(update,insert,delete ) auditing

Ashwan 521 Reputation points

Hi guru We have SQL server 2016 enterprise edition and working on business need to audit some files where data rows insert, update, delete prior history including

  1. who has action
  2. date
  3. what SQL as used to execute
  4. before image of the record
  5. after image of the record(may be can use current record in the table)
  6. deleted record (prior to delete rec info)

I evaluate database auditing and CDC but I dont think SQL Server has a one solution to supply all the audit information .

CDC audit : does not have information where who has done the changes

Please let us know any one have such requirement and how they achieved it


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,069 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Seeya Xi-MSFT 16,451 Reputation points

    Hi @Ashwan ,

    Yes. As you said, > I evaluate database auditing and CDC but I dont think SQL Server has a one solution to supply all the audit information.
    You can use the view log method. ApexSQL Log is a well-known, 3rd party SQL Server transaction log reader. You can choose the table and operations for table as below screenshot:

    Best regards,

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Ashwan 521 Reputation points

    Hi SeeyaXi, Thank you for the reply . However given information with T log reader , does not capture old record of the row(before modify/delete). We are planing to generate reports based on the audits records . so not sure third party software T log review data can extract /load in to the any custom table(periodically ..say every 2 hours ) and use for the report purpose through our own dashboard
    ex: 111,ryry,06/08/2021 (before)

    I still wonder why SQL server does not have one single solution where i can find CDC +database-audit-specification +user edited, hostname , program . interesting is required information already available with in SQL server . Oracle database has this capability for audition in single solution