Execute Stored Procedure that cannot be manually stopped

Question

I am looking for a solution to:

1. Allow an admin to manually run a stored procedure
2. Once the stored procedure runs the same admin cannot stop it using the "Cancel Executing Query"
3. The stored procedure must be invoked from SSMS ONLY.
4. No other external program or language except for MS Management Studio and TSQL can be used.

I have tried creating a stored procedure than invokes another stored procedure, but it did not work. Any ideas are welcome....

Answer 1

You could create a stored procedure that invokes another stored procedure asynchronously either by spawning a SQL Server Agent Job or using Service Broker.

It would still be possible for someone with sufficient permissions to stop the procedure from executing though (but not via the "Cancel Executing Query" button as your first procedure would return immediately with execution of the main procedure being carried out on a different spid).

Answer 2

What you want is, by definition, impossible. Because the definition of admin is: the user to which no privilege can be denied. Therefore any attempt to do something that has the phrase 'admin cannot' is flawed from the get go, because the admin will, simply, always 'can'.

There are more fallacies in your reasoning, like the belief that there is any way to make privilege decision based on untrusted input. Case in point, the requirement that The stored procedure must be invoked from SSMS ONLY.. This is a security decision (privilege to run the procedure) based on an untrusted input (the APP_NAME()).

What I suggest is rather than ask us how to implement what you already concluded must be the solution, better lay down your requirements and let us suggest possible solutions to your problem.

Answer 3

There is no way to fully achieve what you are asking for, and there should not be. A stored procedure that an administrator cannot force to halt and rollback would be contrary to the concept of what an administrator should be able to do and could cause many problems in practice.

As Martin described, you could make it slightly harder to cancel by causing it to spawn an external process that is then independent of that administrator's connection, but it could still be terminated, it would just require additional steps. Also, forcing it to spawn another process could cause problems of its own, such as making it difficult to receive any data that was reutred by the procedure.

Also, there is no real way to keep someone who is logging in with an admin account from something other than SSMS from running a stored procedure, and there shouldn't be. Command Line Alternatives such as OSQL from MS were meant to be able to fully control SQL Server without resorting to any GUI. As Shark pointed out, you could try to grant execute only to a specific user and only log in from that user account. But an admin could run it without being specifically granted permissions. Even if you tried to DENY execute to an admin, they could just remove that denial. You could if you really wanted to query the application name in your procedure using the APP_NAME() function and then raise an error if it was not 'Microsoft SQL Server Management Studio' but an Admin could get around even that if they wanted to (spoofing the app name, or even altering the procedure to disable that part before they ran the rest of it).

Incidentally, http://www.codeproject.com/Articles/80903/Restricting-Access-to-Database-by-Application-Name talks a bit more about restricting access to a database based on app name, but an administrator could get aroundt hat easily.

The bottom line is that if dealing with a limited user, you can exercise a fair bit of control and stringently limit them in what they can do. When talking about an admin, you cannot and should not be able to limit them overly much. If you feel you need to limit an admin, then this person should not be an admin.

Answer 4

The only way to prevent an application (besides SSMS) from executing a stored procedure is to have a database user that is only utilized by SSMS. You can just GRANT EXECUTE on that stored procedure for that user only.

There would be no way to prevent the halting of the execution of that stored procedure. Any login with processadmin server role (or sysadmin) will be able to kill the process, and the calling user will be able to stop execution.