Current transaction ID in an audit trigger

Question

I was looking at storing some form of transaction id from an audit trigger. The solution appeared to be to use sys.dm_tran_current_transaction as in this post SQL Server Triggers - grouping by transactions.

However, I cannot use this because the user account running sql statements will not have the "VIEW SERVER STATE" permission and results in the error:

Msg 297, Level 16, State 1, Line 3
The user does not have permission to perform this action.

Does anyone know of an alternative to this view that will provide a similar transaction id or a way to use "WITH EXECUTE AS" on the trigger to allow selecting from this view.

From my attempts at "WITH EXECUTE AS" it appears that server level permissions are not carried over, which is expected really since it is impersonating a database user.

score 13 · Accepted Answer · answered Aug 12 '09 at 17:29

You can resolve almost any security problem using code signing. Most granular and finely tuned access control, is just a bit on the hard side to understand.

Use EXECUTE AS OWNER on the trigger, create a certificate, sign the trigger, drop the private key (so that noone else can use it to ever sign anything again), export the certificate (public key only), import the certificate in master, create a login derived from the certificate, grant authenticate to this login (in order to extend the database execute as impersonation), then grant view server state to this login. This is bullet proof, perfectly controled priviledge control. If the trigger need to be changed, the signing process (including the cert derived login and grants) have to be done again. From a security point of view, this is desired (you are signing a specific variant of the trigger), from operational point of view is rather a pita, but is manageable.

create table t (i int);
create table audit (transaction_id int);
go

create trigger t_audit_trigger
on t
with execute as owner
after insert, update, delete
as
begin
    set nocount on;
    insert into audit (transaction_id) 
    select transaction_id from sys.dm_tran_current_transaction;
    if (@@ROWCOUNT != 1)
        raiserror(N'Failed to audit transaction', 16, 1);
end
go

create certificate t_audit_view_server
    encryption by password = 'Password#123'
    with subject = N't_audit_view_server'
    , start_date = '08/10/2009';
go

add signature to t_audit_trigger
    by certificate t_audit_view_server
    with password = 'Password#123';
go

alter certificate t_audit_view_server
    remove private key;

backup certificate t_audit_view_server
    to file = 'c:\temp\t_audit_view_server.cer';
go

use master;
go

create certificate t_audit_view_server
    from file = 'c:\temp\t_audit_view_server.cer';
go  

create login t_audit_view_server_login
    from certificate t_audit_view_server;
go

grant authenticate server to t_audit_view_server_login;
grant view server state to t_audit_view_server_login;
go

Thanks for the additional link to the msdn topic on the subject, very informative. — Chris Chilvers, Aug 12 '09 at 22:48
Welcome to the club lol. With you, we are now 4 people on the planet that actually read that article. — Remus Rusanu, Aug 12 '09 at 22:55
If you have many triggers, you could query the view inside an user defined function, and sign that function with the certificate (As suggested in the reply #6 at http://dbaspot.com/sqlserver-programming/197082-there-any-function-statement-return-transaction-identific-id-between-begin-trans-commit-trans.html). Then you can use that function inside all the triggers. Therefore, is a single object you must re-sign when you decide to make a change. — Guillermo Gutiérrez, Mar 21 '13 at 20:45

score 3 · Answer 2 · answered Dec 09 '16 at 10:43

From SQL Server 2008, Microsoft introduced sys.dm_exec_requests, which is to deprecate sys.sysprocesses. This view returns the transaction_id, and can be called without granting VIEW SERVER STATE. Like sys.sysprocesses, it returns details for the current process if VIEW SERVER STATE is not granted, and all processes if it is.

John Sansom · Answer 3 · 2009-08-12T12:28:38.607

Although not directly answering your question, rather than using a custom built auditing framework, in SQL Server 2008 you could make use of the Change Data Capture Technology.

See the following reference from Books Online: http://msdn.microsoft.com/en-us/library/bb522489.aspx

EDIT (Solution, added): Here is a walkthrough of how to create a stored procedure to access the system view, making use of the execute as clause and using impersonation.

USE MASTER;

select * from sys.dm_tran_current_transaction

--Create a login with view server state permissions
CREATE LOGIN ViewServerStateLogin
WITH password = 'Hello123';
CREATE user ViewServerStateLogin;

--Create a login to test the permissions assignment
CREATE LOGIN TestViewServerState
WITH password = 'Hello123';
CREATE user TestViewServerState;

--Test with Login
EXECUTE AS LOGIN = 'TestViewServerState';
    --This obviously does not work.
    select * from sys.dm_tran_current_transaction
revert;

--Grant view server state permission to the ViewServerStateLogin
GRANT VIEW SERVER state TO ViewServerStateLogin;

--Create a procedure to wrap the call to the system view
CREATE PROCEDURE proc_TestViewServerState

AS
    SET NOCOUNT ON;

    EXECUTE AS LOGIN='ViewServerStateLogin'

        select * from sys.dm_tran_current_transaction

    revert;

RETURN(0);

--Assign execute permission to the test accounts
GRANT EXECUTE ON proc_TestViewServerState TO TestViewServerState

--Grant impersonation rights to the test login
GRANT IMPERSONATE ON LOGIN::ViewServerStateLogin TO TestViewServerState

--Test with Procedure
EXECUTE AS LOGIN = 'TestViewServerState';
    EXEC proc_TestViewServerState
revert;

The only problem with granting TestViewServerState the impersonate permission is that user can then also do: EXECUTE AS LOGIN='ViewServerStateLogin' select * from sys.dm_tran_database_transactions AS dtdt revert I tried using a execute as on the procedure, but again impersonate is a server level permission, and thus is lost when impersonating the database user. — Chris Chilvers, Aug 12 '09 at 11:37

score 2 · Answer 4 · answered Apr 05 '18 at 10:26

2

Starting with SQL Server 2016, you could use CURRENT_TRANSACTION_ID. According to the docs:

Any user can return the transaction ID of the current session.

answered Apr 05 '18 at 10:26

Branko Dimitrijevic

50,809
10
93
167

score -1 · Answer 5 · answered Oct 19 '10 at 19:32

-1

Encrypt the stored procedure and don't share the pw for ViewServerStateLogin. Then you get a black box of sufficient density to satisfy the auditors.

answered Oct 19 '10 at 19:32

don

1

Current transaction ID in an audit trigger

5 Answers5

Linked