How to Encrypt a Stored Procedure in SQL Server

Encrypting a SQL Server Stored Procedure

If you have any complex business logic that you want to keep secure and hide from end user due to some security reasons then you can encrypt SQL Server Stored Procedure.

Once you encrypt the Stored Procedure, you will not be able to make any changes in stored procedure and can not see stored procedure definition either.

To demonstrate this, first we Create a sample table named SalesByYear and Insert some records into this table and then create a Stored Procedure. that will return the Sales data from table SalesByYear.

CREATE TABLE dbo.SalesByYear
(
SalesId INT NOT NULL,
SalesExecutiveId INT NOT NULL,
Year INT NOT NULL,
Amount NUMERIC(9,2)
)
INSERT INTO dbo.SalesByYear
(SalesId, SalesExecutiveId, Year, Amount)
VALUES (1, 501, 1989, 1000),
(2, 501, 1990,2000),
(3, 501, 2000,3000),
(4, 501, 2001,4500),
(5, 501, 2002,5000),
(6, 501, 2003,6000),
(7, 501, 2004,3000),
(8, 501, 2005,5500),
(9, 501, 2006,8500),
(10, 501, 2007,9000),
(11, 502, 1989, 1550),
(12, 502, 1990,2500),
(13, 502, 2000,34000),
(14, 502, 2001,4510),
(15, 502, 2002,5600),
(16, 502, 2003,6500),
(17, 502, 2004,6000),
(18, 502, 2005,7500),
(19, 502, 2006,8000),
(20, 502, 2007,9600)

Now we will create a stored procedure with one input paramertes vaue for year and returns sales for that year.

CREATE PROC USP_FetchSales_ByYear
@Year INT
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM dbo.SalesByYear WHERE YEAR =@Year

END

Once you complie the stored procedure, you can see it in object explorer also can modify it as shown below.

 

Also you can view stored procedure definition using T-SQL query that gets the definition of Stored procedure as shown below.

SELECT O.name, M.definition, O.type_desc, O.type 
FROM sys.sql_modules M 
INNER JOIN sys.objects O 
ON M.object_id=O.object_id 
WHERE O.type IN ('P')

As you can see, stored procedure definition can be seen in definition column of result.

Lets also check whether the SP is working correctly and returing data or not.

Lets see the sales details for year 1989, for this you need to pass value for year parameter as 1989.

EXEC USP_FetchSales_ByYear 1989

As you can see, SP returns the data for sales year 1989.

So far, you have created a stored procedure and check that you are able to modify it also can see the definition, and execute it to see sales for sepcific year.

Encrypting SQL Server Stored Procedure

Before Encrypting stored procedure, it is recommeded to keep a back copy of stored procedure with you for future use as once stored procedure is encrypted you will not be able to modify any changes in stored procedure also can not see stored procedure definition.

Lets encrypt the Stored Procedure USP_FetchSales_ByYear using keyword With Encryption as shown below.

ALTER PROC USP_FetchSales_ByYear
@Year INT
WITH ENCRYPTION
AS
BEGIN

SET NOCOUNT ON;
SELECT * FROM dbo.SalesByYear WHERE YEAR =@Year

END

As you can see, commands completed successfully, Lets go to object explorer and check whether you are able to modify stored procedure or not.

And you can see, modify option is disabled so you can not make any changes in the stored procedure and that is the purpose of making stored procedure encrypted.

 

Lets check, can you still see the definition of stored procedure or not.

If you also try to see the definition of stored procedure using SQL Server System Stored procedure sp_helptext, you will get following message.

Sp_HelpText USP_FetchSales_ByYear

Lets see, whether you still able to use the stored procedure or not.

EXEC USP_FetchSales_ByYear 1989

You can see it returns the data for year 1989, that means you can also use encrypted sp but can not see sp definition and can not modify it.

SQL Server Stored Procedure.

Get the definition of stored procedure, User Defined Functions,  & Triggers

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 5,238 total views,  1 views today