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
You may try temporal tables
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.
{count} votes
-
Ronen Ariely 13,776 Reputation points Microsoft MVP2020-09-12T12:05:50.37+00:00 Good day
audit modified columns values... DML operations of columns
What exact information do you need to collect?
For example, Do you need to the values as well or only to monitor what was changed? Do you need information on who made the change?
without triggers and CDC.
Why?!?
There are multiple option you can use. Some built-in the server and some are external tools. In order to help you find the best solution to fit your needs we need more information. For example why not a tool that based on triggers or CDC ?!?
In the meantime, check the following post. It is very nice and short post which present the most common options for auditing: https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/
-
RatheeshKNair 6 Reputation points2020-09-22T09:48:13.863+00:00 You can use temporal tables if 2016 and higher . If you dont want the data to be in same database then you can use Auditing.
Sign in to comment
5 answers
Sort by: Most helpful
-
-
answered
2020-09-12T07:11:13.21+00:00 
SQLZealots 276 Reputation pointsYou may try Temporal Tables
-
answered
2020-09-12T12:38:52.14+00:00
tibor_karaszi@hotmail.com 4,291 Reputation points Microsoft MVPRonen 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.
-
answered
2020-09-14T07:20:42.353+00:00
Cris Zhan-MSFT 6,561 Reputation pointsHi @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.-
Cris Zhan-MSFT 6,561 Reputation points
2020-09-22T09:40:18.557+00:00 Hi @BHVS ,
Is there any update on this case?
If the answer is helpful, please click "Accept Answer" and upvote it.
Sign in to comment -
-
answered
2020-09-22T15:48:37.597+00:00
Stefan Hoffmann 616 Reputation pointsWithout using triggers or CDC, you're left with events, traces, logs and proxies. All approaches more complex to implement then triggers or CDC.