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?