Trigger spid

Question

Trigger spid

Whisper9999

Hall of Fame

Points: 3012
More actions
January 17, 2012 at 11:09 am

#263028

Is there any way to pull the spid of the job/process/query that triggered a trigger?
I'm hoping someone knows of a nifty trick to do this as we can then tie this spid back to some monitoring software that we have to figure more about who did what.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

spids get reused, as they disconnect and someone else reconnects; You might want to add a trace so you can get the data you want int eh future, complete with username, hostname, and everything available in the trace.

AFAIK, if you don't have something to audit with in place, you can't track it back to get whodunnit info.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

padhis SSCommitted Points: 1762 More actions · Answer 2

Here is a good example for this.

http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm

Whisper9999 Hall of Fame Points: 3012 More actions · Answer 3

Thx. Hey, I found that you can just use @@spid. Seems to work just fine based on my preliminary testing.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

The point Lowell was making is that unless you are cross referencing real time the spid is inaccurate at best. They get recycled so a spid of 42 might belong to me, but tomorrow it could be a .net app and the day after it could be an SSIS package. If however, you are able to cross reference real time, then you got exactly what you were looking for.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 5

inside a trigger, or procedure, there's a lot of built in functions you can use;

I use a variation of this in a lot of auditing type stuff:

--the auditing snippet below works fine in a

--login trigger,

--database trigger

--or any stored procedure.

-- you might want to GRANT SELECT ON sys.dm_exec_connections TO PUBLIC , so you can get the IP address and NT Authorization Scheme

-- as normal users don't have access to that view.

SELECT

getdate() AS EventDate,

DB_NAME() AS DBName,

HOST_NAME() AS HostName,

APP_NAME() AS ApplicationName,

OBJECT_NAME(@@PROCID) AS ProcedureName,

USER_ID() AS Userid,

USER_NAME() AS UserName,

SUSER_ID() AS sUserid,

SUSER_SNAME() AS sUserName,

IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

IS_MEMBER('db_owner') AS [Is_DB_owner],

IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

client_net_address AS ipaddress,

auth_scheme AS AuthenticationType

FROM sys.dm_exec_connections where session_id = @@spid --requires permissions on sys.dm_exec_connections !!!!!!!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!