0

In SQL Server 2016, I have a stored procedure that sends an email, which I call from various other stored procedures.

As part of that email procedure, I'd like to log the results to a table, including the name of the stored procedure that was used to call the email procedure.

If I use:

SELECT OBJECT_NAME(@@PROCID)

then all I get is the name of the email procedure, which I already know.

What I want is the name of the procedure that ran the email procedure, if that makes sense.

Is this possible?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Geoff Griswald
  • 937
  • 12
  • 24
  • 2
    Change your email `procedure` to accept another `parameter`, and pass the calling `procedure` name into it? – Ryan Wilson Jan 15 '20 at 15:42
  • Could do, but then anyone that used the email proc could fake that paramater simply by passing in some other varchar. – Geoff Griswald Jan 15 '20 at 15:43
  • 4
    Short answer: without explicit cooperation from the parent passing in this info, no. I know this because I've once spent considerable time looking into it. You can't even get a call stack in T-SQL; this has been a feature request for over 15 years now. – Jeroen Mostert Jan 15 '20 at 15:43
  • Thanks Jeroen, not the answer I was looking for but saves me heading down the same dead end I guess :) – Geoff Griswald Jan 15 '20 at 15:44
  • @GeoffGriswald "Anyone who used that email proc"? How open is this for use? If it's just being used by other procedures, lock it down so that it can't be called from outside of sql-server. – Ryan Wilson Jan 15 '20 at 15:47
  • 1
    Note that if you are willing to change the parent sprocs, you can at least mitigate the spoofing issue by using `SESSION_CONTEXT` to pass this. When combined with proper permissions for calling procedures (and/or checking user names with `SUSER_NAME()` and/or using the `@read_only` parameter of `sp_set_session_context`), you can minimize the risk of someone setting this value incorrectly (by accident or on purpose). – Jeroen Mostert Jan 15 '20 at 15:48
  • Something is horribly wrong in your organization if "... could fake that parameter ..." is an issue. – SMor Jan 15 '20 at 16:29
  • Indeed yes. Sorry. I'll go fix my organisation now. – Geoff Griswald Jan 15 '20 at 16:33

0 Answers0