SQL Server 2000: Securing Your Stored Procedure

This tutorial explains how to secure your stored procedures if you ever need to.  Procedures resides on the database server in open fashion. It means that every body can easily access and view its code.

Securing Your Stored Procedure

Stored Procedures resides on the database server in open fashion. It means that every body can easily access and view its code. They are present in “syscomments” system table in each database and completely accessible.

In order to secure it, we can encrypt them by using “WITH ENCRYPTION” keyword. Remember that encrypting stored procedures is a single step process and you can’t decrypt it i.e. vice versa is not possible. So always encrypt you stored procedure after making sure that you will not have to change it further. I am using the previous example of OUTPUT parameters and encrypting it.

CREATE PROCEDURE Show_Customer
@FirstNo int,
@SecondNo int,
@Result int OUTPUT
WITH ENCRYPTION
AS

SET @Result = @FirstNo + @SecondNo

When you try to open the encrypted stored procedure, you will see the window somewhat like

i.e. SQL Server will not allow you to view the contents of the procedure. Again reminding you, decryption of an encrypted procedure is not possible.