How to find who called a stored procedure

15 pts.
Tags:
SQL Server
SQL Server 2005
SQL Server 2005 SP3
Stored Procedures
I would like to know what process or stored procedure called one of my stored procedures. Is there any way of getting this information from SQL Server 2005? Thanks in advance, Ed

Software/Hardware used:
SQL Server 2005 SP3
ASKED: May 26, 2010  2:37 PM
UPDATED: May 28, 2010  6:35 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

select ORIGINAL_LOGIN()
- this will return the user account login, usually like database\username

select SYSTEM_USER
- generally the same result, but if the context was changed, this will reflect the current user context instead of the original login above

I don’t know of a way to get the name of the procedure that actually called your procedure.

However, you can easily find out the names of all the defined procedures/functions that directly call your procedure (at least where they were compiled with a reference to your procedure).

<pre>select so.name ‘Object Name’, so2.name ‘Dependent On’ –, sd.*
from sys.sysdepends sd
join sys.objects so on so.object_id = sd.id
join sys.objects so2 on so2.object_id = sd.depid
where so2.name = ‘ my procedure name ‘</pre>
Of course, this won’t show any calls that are dynamically generated – only those that were compiled.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Twlp123
    When a process/stored procedure calls a stored procedure, I would pass a string parameter as the name of the process/stored procedure to the called stored procedure. The called stored procedure will receive this parameter and returns the name or update a log file.
    165 pointsBadges:
    report
  • Olivera
    Thanks to both of you.
    15 pointsBadges:
    report
  • Kccrosser
    If you were creating new code, adding a "trail" of breadcrumbs is not a bad idea. From the problem description, it sounded like the code already existed, so modifying all the callers wasn't a likely option (and there must be more than one, otherwise the question wouldn't be needed). One caveat about the sys.sysdepends table mentioned above: it is possible (indeed, very likely) that NOT all the dependencies are properly recorded in the table. In order to make sure that all the dependencies are in there, you should first force an update of the dependencies table by calling: exec sp_refreshsqlmodule @nameofmodule A simple script to update all dependencies would be:
    declare @name varchar(255);
    declare cur cursor for
    select name
    from sys.objects (nolock)
    where type in ('FN','P','TF','IF','V','TR')
    open cur;
    fetch next from cur into @name;
    while @@FETCH_STATUS = 0
    begin
    	begin transaction;
    	begin try
    		exec sp_refreshsqlmodule @name;
    		commit;
    	end try
    	begin catch
    		rollback;
    	end catch;
    	fetch next from cur into @name;
    end;
    close cur;
    deallocate cur;
    Note - because sp_refreshsqlmodule is updating a table, it needs to be in a transaction. If there are SQL modules that will throw errors when "refreshed", the transaction must be rolled back. Thus the above try..catch logic. After this is run, the sys.sysdepends table will be correct.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following