SQL Server Stored Procedure with Encryption and Recompile Attribute

Back to: SQL Server Tutorial For Beginners and Professionals

SQL Server Stored Procedure with Encryption and Recompile Attribute

In this article, I am going to discuss the SQL Server Stored Procedure with Encryption and Recompile Attribute with examples. Please read our previous article where we discussed the Temporary Stored Procedure in SQL Server with examples. As part of this article, we are going to discuss the following important concepts.

1. Learning Some useful system-defined stored Procedure in SQL Server.
2. How to view the text of a stored procedure?
3. How to encrypt the text of a stored procedure in SQL Server?
4. Why do we need to encrypt the text of a stored procedure?
5. Understanding the With Encryption Attribute in SQL Server Stored Procedure.
6. Understanding the With Recompile Attribute in SQL Server Stored Procedure.

Learning Some useful system-defined stored Procedure in SQL Server.

Let’s have a look at some of the useful system-defined procedures that 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 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 stores 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 information about the functions, procedures, etc which depends on this table

sp_depends spGetEmployee : it gives 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.

SQL Server Stored Procedure Encryption and Recompile Attribute:

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

1. With Encryption Attribute
2. With Recompile Attribute

Understanding the With Encryption Attribute in SQL Server Stored Procedure:

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-defined stored procedure. If you try to view the text using the sp_helptext system stored procedure, then we will get a message stating ‘the 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 us understand the use of the SQL Server Stored Procedure Encryption option with one example.

We are going to use the following Employee table

Please use the 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 retrieves the data from the text column of the syscomments table. When we execute the above statement it will give us the below output.

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.

Note: When an application is developed for a client at the time of installing this application on the 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 in SQL Server Stored Procedure:

Whenever a procedure is compiled for the first time it prepares the best execution 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 execution 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 the Recompile Attribute. Then it is forced to be compiled each time it is executed and whenever it compiles it prepares the execution plan.

Forcing a procedure for recompilation and prepared the execution 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 are 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

In the next article, I am going to discuss the User-Defined Functions in SQL Server with examples. Here, in this article, I try to explain the SQL Server Stored Procedure with Encryption and Recompile Attribute with some examples. I hope now you got a better idea of when to use and how to use the Encryption and Recompile Attribute in SQL Server.