Articles → SQL SERVER → Stored Procedure Encryption In SQL Server

Stored Procedure Encryption In SQL Server

This article describes encrypting stored procedures in the SQL server.

Software Requirement

Before reading this article, ensure that Microsoft SQL server is installed on your machine.

Prerequisite Knowledge

Before proceeding, you should have a basic understanding of stored procedures in SQL Server.

What Does Encryption Mean?

Encryption transforms readable data into an unreadable format, ensuring that only authorized parties can access the original information.

Syntax

In SQL Server, stored procedures can be encrypted to protect against unauthorized access. The syntax for creating an encrypted stored procedure is as follows: -

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

Executing sp_helptext on this stored procedure yields the following result.

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

Generating the stored procedure script results in the 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: -

Stored procedure definitions should be stored in a separate file, as encrypted procedures cannot be retrieved from SQL Server Management Studio. If this file is lost, the definition is irrecoverable.
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	**