You may try temporal tables
Auditing table columns without triggers in sql server
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.
5 answers
Sort by: Most helpful
-
-
SQLZealots 276 Reputation points
2020-09-12T07:11:13.21+00:00 You may try Temporal Tables
-
tibor_karaszi@hotmail.com 4,311 Reputation points
2020-09-12T12:38:52.14+00:00 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.
-
Cris Zhan-MSFT 6,626 Reputation points
2020-09-14T07:20:42.353+00:00 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. -
Stefan Hoffmann 621 Reputation points
2020-09-22T15:48:37.597+00:00 Without using triggers or CDC, you're left with events, traces, logs and proxies. All approaches more complex to implement then triggers or CDC.