Post
Ask Question

How To Get A Stored Procedure Encrypted In SQL Server

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.

  1. create table Products  
  2. (  
  3.    ID int primary key identity(1,1),  
  4.    ProductName nvarchar(50),  
  5.    ProductImage nvarchar(50),  
  6.    Description nvarchar(max),  
  7.    Category nvarchar(50),  
  8.    Price int  
  9. )  

Step 2

Create a stored procedure in it.

  1. create procedure spAddProduct  
  2. (  
  3.    @ProductName nvarchar(50),  
  4.    @ProductImage nvarchar(50),  
  5.    @Description nvarchar(max),  
  6.    @Category nvarchar(50),  
  7.    @Price int  
  8. )  
  9. as  
  10. begin  
  11.    insert into Products(ProductName,ProductImage,Description,Category,Price)  
  12.    values(@ProductName,@ProductImage,@Description,@Category,@Price)  
  13. end  

Step 3

Alter the stored procedure to make it encrypted.

  1. Alter procedure spAddProduct  
  2.    @ProductName nvarchar(50),  
  3.    @ProductImage nvarchar(50),  
  4.    @Description nvarchar(max),  
  5.    @Category nvarchar(50),  
  6.    @Price int  
  7.    WITH Encryption  
  8. as  
  9. begin  
  10.    insert into Products(ProductName,ProductImage,Description,Category,Price)  
  11.    values(@ProductName,@ProductImage,@Description,@Category,@Price)  
  12. end  

Check the stored procedure text

sp_helptext spAddProduct

SQL Server

Step 4

Check if the stored procedure is encrypted.

sp_helptext spAddProduct

SQL Server