Hide Store Procedures

Question

Dear Friends Is there any way that We can Hide Store Procedures in SQL Server from users

thanks in advance

Answer 1

You can encrypt the text of the stored procedure, if that is what you mean.

CREATE PROCEDURE my_procedure
WITH ENCRYPTION
AS
BEGIN
  SELECT *
  FROM my_table
END

The encryption is not unbreakable, but at least it is a first line of defence.

Answer 2

For SQL Server 2005 and above, don't give them permissions. Then, "Metadata visibility" means it's not visible and not runnable. It makes no sense to want to hide them but give them permissions.

Note: db_owners and syadmins will always see them.

Otherwise your only option is to encrypt the stored procedure as mentioned (which is easily defeated using free tools).