Reader Level:
Article

Encryption of Stored Procedure in SQL Server

By Deepak Middha on Nov 04, 2012
In this article I described encryption of Stored Procedures in SQL Server.

Introduction

Today we are going to learn how to encrypt the logic of a Stored Procedure from the end user or others. There are several reasons to hide the logic of a Stored Procedure. The most important reason is security. We encrypt the Stored Procedure using the "with encryption" keyword.

I assume you have basic knowledge of Stored Procedures. For more help visit, Stored Procedure in SQL Server.

First of all we create a table named student. After that we create a Stored Procedure named usp_details without encryption then we show the logic of that Stored Procedure with the help of command. After that we create another encrypted Stored Procedure named usp_details1. And show that we can't see the logic of the encrypted Stored Procedure.

Creation of table:

create table student(studentId int, studentName Varchar(15), studentAdd varchar(25))

Insertion of data:
 

insert into student

select 1,'Deepak','Delhi'union all

select 2,'Arora','Punjab'union all

select 3,'Ashu','tarapur' 


Output:
 

select * from student


encryption-of-stored-Procedure-emp.jpg

Creation of unencrypted Stored Procedure:
 

create proc usp_details

as

select * from student 


Executing this Stored Procedure:
 

exec usp_details


Output:

encryption-of-stored-Procedure-usp_details.jpg

Logic of this Stored Procedure:

exec
 sp_helptext usp_details

Output:

encryption-of-stored-Procedure-logic.jpg

Creation of encrypted Stored Procedure:

create
 proc usp_details1

with encryption

as

select * from student 


Executing of this Stored Procedure:

exec
 usp_details1

Output:

encryption-of-stored-Procedure-usp_display1.jpg

We can't see the logic of the encypted Stored Procedure:

exec
 sp_helptext usp_details1

Output:

If we try to see the logic of the encrypted message it shows the following message:

encryption-of-stored-Procedure-message.jpg

Summary

In this article I described encryption of Stored Procedures in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.

Deepak Middha

Deepak Middha is one time Microsoft MVP(Client App Dev) , working as Sr. Software Developer with lots of hands on experience using ASP.NET 2.0/3.5, AJAX, MVC, C#,  SQL Server 2005/2008/2012, WCF, JQuery and Silverl... Read more

COMMENT USING