Share via

Database objects DML(update,insert,delete ) auditing

Ashwan 521 Reputation points
2022-11-10T00:55:55.667+00:00

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 .

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-audit-specification-transact-sql?view=sql-server-ver16

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

thanks

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,361 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,461 Reputation points
    2022-11-10T08:47:03.917+00:00

    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:
    259082-image.png

    Best regards,
    Seeya

    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
  2. Ashwan 521 Reputation points
    2022-11-13T03:35:29.84+00:00

    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
    thanks