How do I identify the program that called a stored procedure?

Posted on

Question :

As part of an application database design spec, we’ve been asked to block execution of a set of stored procedures if they have been fired directly from SSMS / osql / sqlcmd and so on; that is, they must be permitted to run only from within the application itself.

When our team questioned if this is even possible, we were shown a demo that limited queries to specific systems (actually it let the query run, but logged to a table where it was running from). However, the procedure that did that is encrypted, so we could not find out how it was done.

How do we accomplish this?

Answer :

Multiple ways to get this information:

SELECT APP_NAME();

SELECT PROGRAM_NAME();

SELECT [program_name] 
  FROM sys.dm_exec_sessions 
  WHERE session_id = @@SPID;

Just keep in mind that it can be spoofed in the connection string or in Management Studio’s connection properties. If I connect using the following parameter, all three of the above will return foobar:

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *