Articles → SQL SERVER → Stored Procedure Encryption In SQL Server

Stored Procedure Encryption In SQL Server

In this article I will explain how to encrypt a stored procedure in SQL server.

Software Requirement

Before you start reading this tutorial, you should make sure that SQL server (2005 or above) is installed on your machine.

Prerequisite Knowledge

Before you go ahead, it is required that you should know basics about stored procedure in SQL server.

What Does Encryption Mean?

Encryption means encode a message so that unauthorized parties can’t understand that message.

Syntax

In SQL server, we can also encrypt a stored procedure to protect it from unauthorized access. To create an encrypted stored procedure, use following syntax: -

CREATE PROC < PROC_NAME > [LIST OF INPUT VARIABLES] WITH ENCRYPTION AS < PROC_BODY >

Example

Consider the following sample stored procedure: -

CREATE PROC sp_GetDBName @db_name varchar(100) WITH ENCRYPTION AS 
SELECT 
  * 
FROM 
  SYS.databases 
Where 
  name = @db_name

Now, if we try to get the content of this stored procedure using "sp_helptext" command, you will get following message

Picture showing the message that shows that the stored procedure is encrypted

Even if you try to get the content by right clicking on the proc and then try to create a script you will get following error.

Picture showing the error message when user tries to generate the script of the encrypted stored procedure

Disadvantage Of Encrypting A Stored Procedure

The main disadvantages of encrypting a stored procedure are: -

There is no simple way to get the stored procedure’s definition if somehow the proc gets deleted from hard disk
Encryption requires an extended handshake and lots of CPU cycles. For high-volume sites, you need a high-performance CPU

Posted By -	Karan Gupta

Posted On -	Saturday, February 2, 2013

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**