How do I get the SQL caller's SPID

Question

I have a multi-user application which makes various calls to SQL to alter values in various tables. Some of those tables have triggers on them to audit changes. I have code in the trigger that attempts to match the SPID value, as returned by @@SPID, with SPID values stored by users as they register for DB access (log in).

However, my testing shows that, at least some of the time, the SPID at the time of the trigger running is not the same as the SPID previously saved. How can i get the SPID of the connection that made the DB change that caused the trigger to run? Or if not the SPID, some value that fairly reliably identifies the currently active SQL connection that made the change?

This is for medium-term debugging, not intended to be part of permanent production code, but must run on a live system for days to weeks.

For more info about tracking what a specific SPID is doing I found this support page to be extremely helpful. https://support.na.sage.com/selfservice/viewContent.do?externalId=26352&sliceId=1 — Zaren Wienclaw, Mar 06 '23 at 19:11

score 1 · Answer 1 · answered Apr 13 '17 at 15:29

1

Rather than using @@SPID you could use the SQL Server function ORIGINAL_LOGIN() which returns the identity of the login that first connected to the instance of SQL Server in that session.

answered Apr 13 '17 at 15:29

Steve Ford

7,433
19
40

Huh... TIL - ORIGINAL_LOGIN() that can be very useful to me. +1 – John Cappelletti Apr 13 '17 at 15:37

score 0 · Answer 2 · edited May 23 '17 at 12:09

SPID will be the same for the entire user session only with a persistent database connection that is not shared by other users. I don't know how much value SPID will provide for auditing, but is seems you are looking for a way to correlate database update activity with a specific user session instance. If you use service credentials rather than those of the end user for the database connection, ORIGINAL_LOGIN won't help for auditing.

You could store a session identifier (e.g. guid) in CONTEXT_INFO or SESSION_CONTEXT() depending on your SQL version for use by the trigger code. See How to add custom attributes to SQL connection string? for code examples.

How do I get the SQL caller's SPID

2 Answers2