SQL Server Encrypting Stored Procedures

The definition of a stored procedure which is not encrypted can be viewed by anybody which is a security risk. Encrypting Stored Procedures implies that the code of the procedures cannot be viewed. This provides security of the procedure. 

To encrypt a stored procedure we use WITH Encryption clause in the definition of stored procedure. Look at the following example. WITH Encryption clause is used in header of the procedure.

Use AppliedkDb
Go
If OBJECT_ID('Employees', 'U') is not null
drop table Employees
Go

CREATE TABLE Employees
(
 Id int
 ,first_name varchar(10)
 ,last_name  varchar(10)
)
Go
INSERT INTO Employees VALUES 
	 (1, 'Amit', 'Kumar')
	,(2, 'Ajay', 'Raj')
	,(3, 'Ritu', 'Sinha')
Go
CREATE PROCEDURE usp_getfullnameEncrypted
@emp_id as int
WITH Encryption
AS
declare @fullname as nvarchar(50)
BEGIN
select @fullname = first_name + ' '+ last_name
from Employees
where Id = @emp_id
print @fullname
END
Go

Run the following code.

EXEC SP_HELPTEXT usp_getfullname 

We know that SP_HELPTEXT system stored procedure is used to look at the definition of database objects such as stored procedures. But if the procedure is encrypted then its definition is not shown when the SP_HELPTEXT procedure is executed. We get the message : "The text for object 'usp_getfullname' is encrypted".