question

NLGoliath avatar image
NLGoliath asked

Find original user in procedure with execute as owner

Hi all, I could use some help with the following issue: I would like to log procedure usage in our DB but noticed that when a procedure has been defined 'with execute as owner' it pretty hard to find out what the calling user is. Example: CREATE PROCEDURE tmp.testProcedureUsage WITH execute AS OWNER AS BEGIN -- main SELECT SUSER_NAME() END -- main I have a user 'AppLogin' and 'DBA' (not the actual names, but you get it) The procedure is created by the DBA user When the AppLogin user executes the procedure, the select returns 'DBA' but I really want to have 'AppLogin'. Is this possible without removing the 'with execute as owner' clause? I have searched the internet, but was unable to find anything. Thanks in advance
execute
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
What about the function ORIGINAL_LOGIN(), does that give you what you need? http://msdn.microsoft.com/en-us/library/ms189492.aspx
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

NLGoliath avatar image NLGoliath commented ·
Who knew it could be that easy! It appears to do exactly what I need Thank you Kev Riley
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.