Change Data Capture for a Specific User Only

Question

Change Data Capture for a Specific User Only

alex.sqldba

SSChampion

Points: 10254
More actions
June 20, 2018 at 6:53 am

#352046

Morning Guys,
I wish to enable change data capture for a handful of tables, though I only want to audit/log when a specific user performs an update.
During general usage where this particular maintenance user is disabled I do not want to log any actions. But on the weekends when that user becomes active I want to only record any changes that he makes.
Some other out of scope users may also be doing work at the same time, but I only want to focus on MrMaintenance.
Is this possible with CDC? I am trying to avoid the trigger route.
Cheers
Alex

Viewing 6 posts - 1 through 5 (of 5 total)

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

Rick-153145 SSCrazy Points: 2823 More actions · Answer 1

alex.sqldba - Wednesday, June 20, 2018 6:53 AM
Morning Guys,
I wish to enable change data capture for a handful of tables, though I only want to audit/log when a specific user performs an update.
During general usage where this particular maintenance user is disabled I do not want to log any actions. But on the weekends when that user becomes active I want to only record any changes that he makes.
Some other out of scope users may also be doing work at the same time, but I only want to focus on MrMaintenance.
Is this possible with CDC? I am trying to avoid the trigger route.
Cheers
Alex

It is kind of possible, CDC is an all or nothing way of collecting data. You can easily have a job that will then copy the data you require to another table to just audit what you need. As you will be reading from the CDC tables and not the live, I don't see this being much of an issue.

In your case though, you might be better off with a trigger based on the user.

alex.sqldba SSChampion Points: 10254 More actions · Answer 2

The reason I didn't want triggers is firstly, this a vendor app and whilst we have control over the SQL Server, we can't get into the guts of the application itself. So any Errors as result of the trigger interfering with something wont be able to be handled or worked out from the application layer. So I was hoping for something transparent to the application.

When you say CDC is an all or nothing approach - what do you mean?

Joe Torre SSChampion Points: 10248 More actions · Answer 3

CDC can't filtered by user. It's a system process capturing changes on the table level from the log.

alex.sqldba SSChampion Points: 10254 More actions · Answer 4

alex.sqldba

SSChampion

Points: 10254

June 22, 2018 at 2:20 am

#1994942

Cheers Joe.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 5

alex.sqldba - Wednesday, June 20, 2018 8:28 AM
The reason I didn't want triggers is firstly, this a vendor app and whilst we have control over the SQL Server, we can't get into the guts of the application itself. So any Errors as result of the trigger interfering with something wont be able to be handled or worked out from the application layer. So I was hoping for something transparent to the application.
When you say CDC is an all or nothing approach - what do you mean?

Heh... so write error free triggers. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)