8

I'm currently working on a stored procedure which modifies metadata for an application. This stored procedure can only be run when the application is offline. I didn't want developers to accidentally call it and break something, so I thought I was clever and inserted this block in the top of the stored procedure:

IF (APP_NAME() NOT LIKE '%SQL Server Management Studio%')
BEGIN
  RAISERROR(N'The stored procedure must be run interactively.', 10, -1, -1);
  RETURN;
END;

This works for normal executions, but when running over a local server group, the application name changes to ".Net SqlClient Data Provider".

I can't find another way to determine whether a query comes from SSMS. I can't discriminate based on user names because our applications use domain accounts. The host name isn't an option, either, because there are disaster scenarios in which accessing SSMS from the database server itself is the expected course of action (and I'd rather not surprise anyone during disaster recovery, even if it is unlikely).

Are there any better ways of determining if a query is coming from SSMS, either directly to one server or from a linked server group?

Improve this question
12
  • do you get the same issue if you run : select program_name from sys.sysprocesses where spid = @@SPID ?
    – Ste Bov
    Nov 2, 2015 at 15:30
  • @SteBov, yes, it still comes up as ".Net SqlClient Data Provider"
    – mgw854
    Nov 2, 2015 at 15:33
  • I'm always getting SSMS reported for this. our web apps (which don't have or run ssms) report as .net sql, but I can't replicate the issue you're describing
    – Ste Bov
    Nov 2, 2015 at 16:07
  • 1
    @mgw854 Trying to help. Might want to consider this a good opportunity to fix an application which should never have had connection strings hard-coded in the code in the first place.
    – Aaron Bertrand
    Nov 2, 2015 at 17:26
  • 1
    Yeah to echo what @AaronBertrand said you should get those connection strings into a central config file ASAP. As it stands now how do you ever change your database password? I doubt you ever do, which means that every employee that touched your code can have access to all the data forever, and if your password is ever leaked then you've made a horrible situation worse because you can't react quickly.
    – Erik
    Nov 2, 2015 at 22:01

1 Answer 1

Reset to default
20

Instead of relying on shaky assumptions on the application name, why don't you just revoke permissions from those users that are not supposed to run the procedure?

You said that developers should not run it accidentally: revoke EXECUTE permissions from them and force them to use a different login to perform this task (if they are supposed to perform it, otherwise keep this privilege for you or the application administrator).

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.