Sql-server – How to see what called a stored procedure

sql server

Is there any way we can find out from where a stored procedure was executed?

I am calling a specific stored procedure from the web, but when I check the SQL Server profiler it showing that entry 4 times; I am suspecting some thing is in the background is getting that method. So if possible what is the source of execution of that stored procedure will be really help to narrow down the issue?

I am using SQL Server 2012

Best Answer

Internally within a stored procedure there is no way (that I'm aware of) to determine the calling proc. There are two workarounds that I use

1. This one wont actually be identifiable for a profiler run but can be useful in auditing.

When calling any SP call it with isnull(@procID,@@procID) (into a value called @procID), this will mean that it will hold the stored procedure ID (which can get you the schema and name) of whatever stored procedure was first called, by anything (be it a person or automated call (note this can technically be spoofed by someone manually adding in the @procID to the initial call)

This is quite a bit of work depending on how many things you want to audit. and generates overheads in your db but will get you exactly what your after just in.

2. The easiest way is each of the different routes into your system has its own login, so you have a login for updating users credentials, a different login for another action. This once again can be a lot of work, but will get you the information you want directly from the profiler, it also will make your system a lot more secure if the logins are stored securely as if one login is compromised then there is only that very specific are is effected someone doesn't have access to your entire system

Aditionally depending on how it is being called there is the potential (if you're using php this isnt possible if you have a more complex back end with iss / .net in the background you may be able to set the program_name field for the login, once again this will show up in the profiler and if you have the proc name your calling you can amend that into the program name (WebLogin-schema.usp_procname for example)

Hope this somewhat helps Ste

Related Solutions

Sql-server – Stored procedure executes too long

Take care when comparing stored procedure execution times to inline SQL execution times.

A stored procedure gets compiled into ONE execution plan to fit all possible parameters. Depending on your query this may not be a good plan for Any parameters, but instead the least-worst for all parameters.

When executing Inline, the optimiser can see both the query AND the parameters. It can then compile a plan specifically for that case. It can take conditions such as OR @param IS NULL and turn it into OR TRUE and then be able to pick indexes and approaches that fit that specifically.

In short, Inline and stored procedures can and do generate different plans with different performance characteristics.

For your particular case, can you show us the definition of the stored procedure?

Execute Stored Procedure Remotely Using Linked Server

Assuming the procedure lives on the remote server: have you tried using the EXECUTE AT command?

DECLARE @RunStoredProcSQL VARCHAR(1000);
SET @RunStoredProcSQL = 'EXEC [Database].[dbo].[StoredProcName]';
--SELECT @RunStoredProcSQL --Debug
EXEC (@RunStoredProcSQL) AT [LinkedServerName];
Print 'Procedure Executed';

That's what I have used successfully in the past. I usually surround the declare/exec in a try catch just in case, but it's currently working on my systems every hour.

Best Answer

Related Solutions

Sql-server – Stored procedure executes too long

Execute Stored Procedure Remotely Using Linked Server

Related Question