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
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
You can create a SQL Server Extended Events Session.
Monitor the sql_batch_completed and sql_statement_completed events, add a filter for your stored procedure name and include client_app_name in your global fields to return. You can also collect a host of other information that may be relevant for your needs such as nt_username.
The best way to get this done would be to ensure that all your applications have the connection string configured with the name of the calling app. That way, whether you're using Profiler/Trace or Extended Events, you will know the name of the app based on capturing the call.
One possible workaround for this is to use Extended Events, specifically the Causality Tracking aspect of an Extended Events session. You can add the obvious events like rpc_completed, and then add security events such as connection opened and user login. With causality tracking enabled, you should be able to see who connected and executed the query. That should move you closer to the application if not directly to it.
