August 12, 2009 at 6:50 am
The Scenario
The database uses triggers to audit inserts, updates and deletes.
Currently the only way to tell what occurred in the same transaction is if their they are modified by the same user and their modified time is fairly close.
It would be beneficial if some sort of transaction id could be added to the audit trigger, this transaction id doesn't need to be garunteed to always be unique, so long as it is reasonably unique with in a short time span (eg a day).
Initially it seems like sys.dm_tran_current_transaction should be the answer.
The Problem
sys.dm_tran_current_transaction requires "VIEW SERVER STATE" permission, which I don't really want to grant to the standard database users.
I tried creating a function to wrap the select to sys.dm_tran_current_transaction and using the "EXECUTE AS" clause. Unfortunately "EXECUTE AS" on a function can only impersonate a database user, as such server level permissions are not applied to the impersonated context.
It is not then possible to have the impersonated user impersonate a login with the required permission because once again, the impersonate permission is a server level permission.
At this point it seems the only way to handle this is to grant the user "VIEW SERVER STATE" and then deny them permission to everything but sys.dm_tran_current_transaction, which in a security point of view is backwards and something I do not wish to do.
Thus, short of this drastic measure does anyone know an alternative to sys.dm_tran_current_transaction or a way to allow a user select permission to just sys.dm_tran_current_transaction?
August 12, 2009 at 4:59 pm
I found the answer courtesy of Remus Rusanu from stack overflow, the solution was to use certificates and code signing of the trigger.
The full example can be found at http://stackoverflow.com/questions/1265386/current-transaction-id-in-an-audit-trigger/1267562#1267562
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply