Sql-server – Prevent a stored procedure from being executed outside SSMS

sql serverssms

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?

Best Answer

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).

Related Question