How do I identify the program that called a stored procedure? 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?
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: