4

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?

Improve this question
0

1 Answer 1

Reset to default
7

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

Improve this answer
4
  • 3
    answer + spoof, Nice!
    – SqlZim
    Commented Dec 20, 2014 at 14:17
  • 1
    can this stored procedure EXECUTE permission be granted on application name or it will with user credential only, not sure so asking?
    – vijayp
    Commented Dec 20, 2014 at 14:30
  • 1
    Thank you @Aaron Bertrand. Your answer was exactly what I am looking for! I dont care much for the spoofing part in my scenario, all we want to do is to prevent certain queries from being executed directly as they may end up bypassing other business rules elsewhere.
    – user52302
    Commented Dec 20, 2014 at 14:50
  • 2
    @vijayp no you cannot grant execute based on application name; that logic has to be inside the procedure.
    – Aaron Bertrand
    Commented Dec 20, 2014 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.