SQL Server Stored Procedure Encryption and Recompile

SQL Server Stored Procedure Encryption and Recompile Attribute

In this article, I am going to discuss the use of SQL Server Stored Procedure Encryption and Recompile Attribute with examples. But before understanding the SQL Server Stored Procedure Encryption and Recompile Attribute concepts lets first have a look at some of the useful system defined procedures which are very important while working with SQL Server.

sp_help procedure name:

This sp_help system defined stored procedure is used to view the information of a stored procedure like parameter names, their data type etc. The Sp_help stored procedure not only used to give the information about a stored procedure but also give information about other database objects like tables, views, triggers, etc. You can also press the ALT+F1 key to get the information when the name of the object is highlighted.

Example:

sp_help spGetEmployeeCountByGender

sp_help Employee

sp_helptext procedure name:

This sp_helptext system procedure is used when you want to view the text of a subprogram such as function and procedures. The most important point to keep in mind is that whenever we created a stored procedure or function then the content  or text of the function or procedure is going to be saved under the syscomments system table

Example:

Select * from syscomments

In the above syscomments table, there is a column called “text” which actually store the complete create procedure or function statement.

The sp_helptext system procedure will retrieve the information from the text column of the syscomments table and then displays it.

Sp_depends procedure name:

The sp_depends system defined stored procedure is used when we want to see the dependency object of a stored procedure. This procedure is very useful when we want to check if there are any stored procedures that are referencing a table that we are going to drop. The Sp_depends system defined stored procedure can also be used with other database objects like table, view, etc.

sp_depends Employee

It gives the information about the functions, procedures etc which depends on this table

sp_depends spGetEmployee

it will give the information on which fields of which table it depends

How to view the text of the stored procedure?

To view the text of a stored procedure you need to use the system stored procedure sp_helptext <Procedure Name>.

Example: sp_helptext spGetEmployeeByGender

Alternatively, right-click on the stored procedure name in object explorer then select Script procedure as Create To new query editor window.

Now we are going to discuss the SQL Server Stored Procedure Encryption and Recompile Attribute

There are two types of attributes that can be used while creating the stored procedure in SQL Server, they are as follows

  1. With Encryption Attribute
  2. With Recompile Attribute
The With Encryption Attribute:

If you want to encrypt the text of a stored procedure in SQL Server then you need to use the With Encryption Option while creating the Stored Procedure. Once you create the stored procedure using the “With Encryption” option then you cannot view the text or content of the stored procedure by using the sp_helptext system stored procedure. If you try to view the text using the sp_helptext system stored procedure, then we will get a message statingthe text for the object is encrypted’.

That means if this With Encryption attribute is used while creating the stored procedure, then the text or content of the stored procedure is encrypted and will not be stored in the text column of the syscomments system table. As a result, we cannot view the text of the stored procedure.

Let understand the use of SQL Server Stored Procedure Encryption option with one example.

We are going to use the following Employee table

SQL Server Stored Procedure Encryption and Recompile Attribute

Please use below SQL Script to create and populate the Employee table

-- Create Employee Table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  DOB DATETIME,
  DeptID INT
)
GO

-- Populate the Employee Table with test data
INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 1)
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 2)
INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060', 2)
INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 3)
INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 1)
INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 2)
GO
Now let’s create one Stored Procedure without using the With Encryption attribute
-- Stored Procedure without with encryption option
CREATE Procedure sp_GetEmployeeDetailsById
(
  @ID INT
)
AS
BEGIN
  SELECT	Name, Gender, CAST(DOB AS DATE) AS DOB 
  FROM	Employee 
  WHERE	ID = @ID
END

Now let’s view the text using the SYSCOMMENTS table as shown below. Have a look at the text column of this table which actually stores the text of our stored procedure.

SELECT * FROM SYSCOMMENTS WHERE ID = OBJECT_ID(‘sp_GetEmployeeDetailsById’)

Now let’s view the text using the sp_helptext system stored procedure as shown below.

sp_helptext sp_GetEmployeeDetailsById

The sp_helptext system stored procedure actually retrieve the data from the text column of the syscomments table.

When we execute the above statement it will give us the below output

SQL Server Stored Procedure Encryption and Recompile Attribute

Now let’s alter the above SQL Server Stored Procedure to use the With Encryption Attribute.
-- Stored Procedure with encryption option
ALTER Procedure sp_GetEmployeeDetailsById
(
  @ID INT
)
WITH ENCRYPTION
AS
BEGIN
  SELECT	Name, Gender, CAST(DOB AS DATE) AS DOB 
  FROM	Employee 
  WHERE	ID = @ID
END

Now if we check the text column of the syscomments system table, then the value of the text column will be null. Please execute the below code to check.

SELECT * FROM SYSCOMMENTS WHERE ID = OBJECT_ID(‘sp_GetEmployeeDetailsById’)

If we try to retrieve the text of the above SP using the sp_helptext sp_GetEmployeeDetailsById.

You will get a message stating ‘The text for object ‘sp_GetEmployeeDetailsById’ is encrypted.As shown in the below image.

SQL Server Stored Procedure Encryption and Recompile Attribute

NOTE: When an application is developed for a client at the time of installing this application on client system we will be using the encryption option on all the views, procedures, functions, triggers, etc and install on the client machine. So that they will not have the chance of viewing the source code or altering the source code.

With Recompiled Attribute:

Whenever a procedure is compiled for the first time it prepares the best query plan according to the current state of the database. Then it executes that query plan when the procedure is called.

The compilation of the procedure and preparing the query plan is prepared not only at the time of procedure creation but each and every time the server is restarted (Implicitly occurs).

If the procedure is created by using with Recompile Attribute. Then it is forced to be compiled each time it is executed and whenever it compiles it prepares the query plan.

Forcing a procedure for recompilation and prepared the query plan is required when the database undergoes significant changes to its data or structure.

Another reason to force a procedure to recompile is if at all the tables is added with new indexes from which the procedure might be benefited forcing for recompilation is very important because we cannot wait until the server is restarted for preparing a new query plan.

NOTE: Even if the “Recompile“ Attribute is available it is not suggested to be used if at all there are no significant changes in the structure of the databases.

Example:

Alter the stored procedure to use the Recompile option.

ALTER Procedure sp_GetEmployeeDetailsById
(
  @ID INT
)
WITH RECOMPILE
AS
BEGIN
  SELECT	Name, Gender, CAST(DOB AS DATE) AS DOB 
  FROM	Employee 
  WHERE	ID = @ID
END
SUMMARY

In this article, I try to explain the SQL Server Stored Procedure Encryption and Recompile Attribute with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *