Auditing table columns without triggers in sql server

asked 2020-09-12T06:12:11.043+00:00
BHVS 61 Reputation points

Hi All,

We have a requirement, we have a one Table have more than 30 columns, i want to audit modified columns values and list(not datatype and column sizes), DML operations of columns,without triggers and CDC.

Thanks in advance.

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

5 answers

Sort by: Most helpful
  1. answered 2020-09-12T07:03:08.307+00:00
    SQLZealots 276 Reputation points

    You may try temporal tables

    No comments

  2. answered 2020-09-12T07:11:13.21+00:00
    SQLZealots 276 Reputation points

    You may try Temporal Tables

    No comments

  3. answered 2020-09-12T12:38:52.14+00:00
    tibor_karaszi@hotmail.com 4,291 Reputation points Microsoft MVP

    Ronen had about the same thoughts and questions that I would raise. What are your needs, and exactly why are you discarding those two technologies that you mention?

    Two things that I'd like to add to the article that was pointed to, which I didn't found discussed much in the article are:

    Do you need to know who did the modification? Many of the techniques don't capture that.

    How easy is the audit log to manipulate? For instance if a trigger logs to a table, then anyone with sufficient permissions can modify that audit log. And a sysadmin cal always do anything inside a SQL Server instance.

    No comments

  4. answered 2020-09-14T07:20:42.353+00:00
    Cris Zhan-MSFT 6,561 Reputation points

    Hi @BHVS ,

    Tracking DML Operations in SQL tables
    https://www.enabledbusinesssolutions.com/blogs/tracking-dml-operations-in-sql-tables


    If the answer is helpful, please click "Accept Answer" and upvote it.
    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.


  5. answered 2020-09-22T15:48:37.597+00:00
    Stefan Hoffmann 616 Reputation points

    Without using triggers or CDC, you're left with events, traces, logs and proxies. All approaches more complex to implement then triggers or CDC.

    No comments