How To Get A Stored Procedure Encrypted In SQL Server

In this blog, we are going to learn how to get a stored procedure encrypted.

Introduction

In this blog, we are going to learn how to create a stored procedure that is encrypted. 

Step1

Create a table in SQL Server with a version of your choice.

create table Products
(
   ID int primary key identity(1,1),
   ProductName nvarchar(50),
   ProductImage nvarchar(50),
   Description nvarchar(max),
   Category nvarchar(50),
   Price int
)

Step 2

Create a stored procedure in it.

create procedure spAddProduct
(
   @ProductName nvarchar(50),
   @ProductImage nvarchar(50),
   @Description nvarchar(max),
   @Category nvarchar(50),
   @Price int
)
as
begin
   insert into Products(ProductName,ProductImage,Description,Category,Price)
   values(@ProductName,@ProductImage,@Description,@Category,@Price)
end

Step 3

Alter the stored procedure to make it encrypted.

Alter procedure spAddProduct
   @ProductName nvarchar(50),
   @ProductImage nvarchar(50),
   @Description nvarchar(max),
   @Category nvarchar(50),
   @Price int
   WITH Encryption
as
begin
   insert into Products(ProductName,ProductImage,Description,Category,Price)
   values(@ProductName,@ProductImage,@Description,@Category,@Price)
end

Check the stored procedure text

sp_helptext spAddProduct

Step 4

Check if the stored procedure is encrypted.

sp_helptext spAddProduct