Track Insert/Update/Delete Operations in Sql Server DB Table

Question

Post reply

Track Insert/Update/Delete Operations in Sql Server DB Table

RCRock

Right there with Babe

Points: 718
More actions
January 31, 2023 at 8:32 am

#4143582

Hi Team,
I have a requirement to track any insert/update/Delete operations happened against Database table by any user, catch those changes and send alert.
Please note that there are around 1000+ DB tables exists so we need to also ensure that performance is not impacted. There are many apps also which insert the data into these tables. Can you please advise?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246873 More actions · Answer 1

Sounds like triggers are your best bet. What sort of alert do you have in mind?

If a user modifies 5,000 rows, do you want 5,000 alerts generated?

If thousands of alerts are being generated, there will be a performance impact. These things don't run on air.

Have you considered writing the modification details to audit tables instead?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 2

I'm with Phil, probably your best bet is a trigger. Here are the docs. You want to look at the DML triggers and, probably, an AFTER trigger, meaning, after the insert/update/delete, not before.

An alternative would be to create an Extended Event session. You'd have to identify all procedures & functions that call the table in question and add them as filtered objects to rpc_completed. You'd also need to catch sql_batch_completed and filter based on the table name.

A trigger in this case is going to be a lot easier to implement. However, I suspect, you may see a lighter load on the system with the Extended Event. Testing is your friend in this, and all cases.

This reply was modified 1 year, 7 months ago by Grant Fritchey. Reason: forgot url

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

kaj SSCommitted Points: 1582 More actions · Answer 3

RCRock wrote:

Hi Team,
I have a requirement to track any insert/update/Delete operations happened against Database table by any user, catch those changes and send alert.
Please note that there are around 1000+ DB tables exists so we need to also ensure that performance is not impacted. There are many apps also which insert the data into these tables. Can you please advise?

That you mention the apps makes me think that you also want to know which app did the insert, maybe even which user did the deed?

This will probably be the hardest nut to crack while setting up table row tracking.

Emperor100 SSCrazy Points: 2161 More actions · Answer 4

Use the Change Data Capture feature. However you have to setup certain days for data retention in the source database and then move the changed data to a different database using last LSN value under the retention duration to lessen the impact on the source database.

=======================================================================

Steve Jones - SSC Editor SSC Guru Points: 735610 More actions · Answer 5

If you have 1000+ tables, and you want anytime a user inserts/updates/deletes, and you send alerts, how many is that? Can they even be processed?

Usually I hear this and I think someone is upset about an incident and wants to prevent it, but they'll never deal with alerts.

Please define better what situations warrant alerts and what criteria you use. If this is any action against any table, then there are ways to do this, but anything more than 1 alert every hour is likely to overwhelm a human's ability to deal with the data.

RCRock Right there with Babe Points: 718 More actions · Answer 6

Hi,

I only want to track user based insert/delete and not the insert/update made by app(automated process). As there are 1000+ tables so I also don't want the performance of database to be impacted.

RCRock Right there with Babe Points: 718 More actions · Answer 7

Hi,

Yes scenario is:

1:App Team , DBA team , Automated App Service Account have permission to do DML operations against table, e.g. they can only do the insert /update/delete against any table of database.

2: I want only to be alerted if App team or DBA team does the insert/update/delete into any of the database table.

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 8

So that requires that you have distinct logins, OR, that everyone logs in and uses the application value on the connection string. Then, you can filter based on the login, or on the application value.

If you're one of those places where everyone connects as 'sa' or everyone connects through a single login, you're going to have a hard time. And the application setting of the connection string is optional, so people could get around it by just leaving that off.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

frederico_fonseca SSCoach Points: 16133 More actions · Answer 9

using the trigger you can filter down logging the change when the user is the service account - and only log the other updates (including host name)

BUT any DBA that wishes to do any update without being noticed can disable the trigger, do the update and enable the trigger and you are back to square one with not knowing who done it/when.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 10

If you have $$ you might look into some audit software like Idera's Compliance Manager. It tracks changes and you can filter it and capture what you want or don't want captured. You can produce date based reports based on databases or users and what activity happened.