Encryption of Stored Procedure 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.


Similar Articles