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.