Auditing table columns without triggers in sql server

Question

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.

Answer 1

You may try temporal tables

Answer 2

You may try Temporal Tables

Answer 3

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.

Answer 4

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.

Answer 5

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