Home > IT Answers > SQL Server > How to find who called a stored procedure
Olivera
15 pts.
 How to find who called a stored procedure
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
RELATED QUESTIONS
Answer Wiki:
select ORIGINAL_LOGIN() - this will return the user account login, usually like databaseusername 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.
Last Wiki Answer Submitted:  May 27, 2010  6:36 pm  by  Kccrosser   3,830 pts.
All Answer Wiki Contributors:  Kccrosser   3,830 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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.

Twlp123
 165 pts.
 

Thanks to both of you.

Olivera
 15 pts.
 

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.

Kccrosser
 3,830 pts.