Stored procedure with optional parameter that doesn't show in SQL Server Management Studio "execute stored procedure" or "script stored procedure"

Question

In SQL Server Management Studio (SSMS), you can execute a stored procedure using the GUI for assistance. This can done by right-clicking on the stored procedure in the Object Explorer and selecting either "Execute Stored Procedure" or "Script Stored Procedure as > CREATE to > New Query Editor Window".

Both of these result in a pre-built SQL query to execute the SP, and both of them include optional parameters from the SP. Is there a way to make it so the optional parameters are "hidden"?

I have some user support folks who use SSMS to run certain SPs, and I don't want them providing values for certain optional parameters. I want to be able to provide them myself, if needed, when I run the SP, but not the user support people.

I've tagged SQL Server 2014 and 2008 R2 in case there's some option I can set in the SP itself.

One way is to wrap procedure with another one and restrict access to some parameters. Then inside this procedure call your original one. — Lukasz Szozda, Aug 08 '17 at 13:46

Lukasz Szozda · Accepted Answer · 2017-08-08T14:07:39.927

You could wrap your stored procedure with another:

CREATE PROCEDURE dbo.my_orig_proc
    @id INT
   ,@some_param_default INT = 10

AS
BEGIN
...
END

Wrapper:

CREATE PROCEDURE dbo.my_wrapper_proc
   @id INT
 AS
 BEGIN
     EXEC dbo.my_orig_proc @id;
 END

I would also restrict access to orignal procedures if necessary.

Another way is to add check and don't allow specific user to override value:

CREATE PROCEDRUE dbo.my_orig_proc
   @id INT,
   ,@some_param_default INT = 10
AS
BEGIN
   IF USER_NAME() = 'user_name' AND @some_param_default <> 10
      RAISERROR('You cannot change @some_param_default value' ,16,1);
END

Drawback: You need to change parameter value in two places and if user has impersonate privilige he still can execute it.

Stored procedure with optional parameter that doesn't show in SQL Server Management Studio "execute stored procedure" or "script stored procedure"

1 Answers1