From a stored procedure in SQL Server, is it possible to get the name of the executable (ie MyApp.exe) that owns the connection? I know there is APP_NAME(), but that appears to just return whatever string was passed into the 'Application Name' parameter in the connection string.
If this is possible, how can it be done? Thanks.
Unless you modify your stored procedure to pass the app name, you're stuck with the results of APP_NAME(). Hopefully, developers are placing meaningful values in there rather than just accepting the default value which is generally an indication of the development tool used to build the app.
Hope this helps,
Bill
Unfortunately I'm not aware of any such thing -- remember that the connection is coming from an entirely different machine most likely.
Perhaps you can design you security so that different apps are using different user names to access the database. Finding the current user is easy.
You can get the computer and the information from the connection string, but that's basically it.
Through monitoring and other measures you can ensure that developers always use Application Name in their connection strings. For instance, you can log cases where it's not an approved applciation name, or use the profiler to watch for things.
Oracle can give you the name of the exe, but SQL Server can't - different architecture.
I realize this isn't exactly what you are looking for. But you can determine a bit of the call stack by using DBCC INPUTBUFFER. If you put logic like this in inside the storeprodedure and log it somewhere you might get some info about the caller.
CREATE TABLE #dbc
(
EventType VARCHAR(15),
Parameters INT,
EventInfo VARCHAR(255)
)
DECLARE @execStr VARCHAR(500)
SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
INSERT INTO #dbc
EXEC ( @execStr
)
SELECT *
FROM #dbc
You can also get the ID of the process on the client machine, so if you had some magical way of mapping that to a process name then you could do it.
