Is there an "are you sure" for stored procedure execution? :)

Question

I have a stored procedure which is doing a lot of delete. Hundreds of thousands of records. It is not going to be runnable from the application, but still, i am concerned, that one of my clients accidentally runs it (i had problems earlier due to their "curiosity") :D

Yes. there are backups and stuff like that, but I was thinking .... not to scare them ... is there a way to ask the user "are you sure?" before executing it? :) thanks

Answer 1

I guess you could have a parameter called "confirmation" that requires a specific string (e,g, "I know what I am doing") to be passed in, if it is not set, or is set incorrectly, just return from the procedure without executing the main code. Not exactly what you wanted, but it is an option.

eg - (untested and probably terrible syntax)

CREATE PROCEDURE dbo.mySproc (
@Confirmation Varchar(100)
) AS
BEGIN
    if(@Confirmation <> 'I know what I am doing')
    BEGIN
        return;
    END
    DELETE from table_name where condition
END

Answer 2

In short, no.

The theory goes that anybody with permissions to find and be able to run a stored procedure, should be allowed. It would be better to restrict permissions so that those with an excess curiosity do not have the permissions to run this.

The other, less secure, option would be to require a pre-defined secret that needs to be passed as a parameter - of course they could just script the stored procedure off to find the secret though...

Of course, the other point would be: If it's not callable, why include it? After all, when you come to do admin type tasks, you can have the statements scripted off as a file that you can keep secure on your own machine