0

I have a relatively simple question that is "Google resistant".

I have a SQL Database Audit spec that logs a transaction_id. I also have a Change Data Capture (CDC) spec that logs __$seqval (LSN). As a sysadmin I can easily correlate the two via sys.fn_dblog().

However, what I'd like to do is set up DML triggers to insert that correlation into a lookup table, and if possible, would like to preserve the context of any user with DML write access to the audited/CDCed table.

To that end, is there a function, DMV or otherwise that can retrieve a Transaction ID when given a LSN - that does not require the sysadmin role to execute/query?

I suspect the answer is probably no, because it doesn't make a whole lot of sense for a user to poke around the transaction log, and if that's the case I'll probably have to take a different route and look at what context I can pull out during the actual DML event.

Thanks!

1 Answer 1

1

is there a function, DMV or otherwise that can retrieve a Transaction ID when given a LSN - that does not require the sysadmin role to execute/query?

Why not run sys.fn_dblog() via a trigger on the CDC table, or otherwise as a scheduled SQL Agent Job, to push the results to a separate table, which your non-admin users have read access to?

1
  • That's probably where I'll land, with an agent job running as sysadmin that pulls out transaction id from sys.fn_dblog() for any LSN in the CDC table and stores the correlated keys. I'd still prefer an after insert trigger (less moving parts so to speak), but don't want to be delegating sysadmin rights to a user just to accomplish that.
    – Vocoder
    Commented Sep 21, 2023 at 0:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.