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

Creation of unencrypted Stored Procedure:
 

create proc usp_details

as

select * from student 

Executing this Stored Procedure:
 

exec usp_details

Output:



Logic of this Stored Procedure:

exec sp_helptext usp_details

Output:



Creation of encrypted Stored Procedure:

create proc usp_details1

with encryption

as

select * from student 

Executing of this Stored Procedure:

exec usp_details1

Output:



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:



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.