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.
Get the definition of stored procedure, User Defined Functions, & Triggers
