Sql server: Encrypt store procedure, view and function

In this article I am going to explain how to Encrypt store procedure, view and function in sql server.

In previous article I have explained how to take database Backup and restore usingcommand T-Sql, Sql server REPLACE function and Sql server TRIM function.

Description:

We write most of logic in Store procedures. Sometime due to security reason we have to hide the logic from end users. Keyword With encryption is used to encrypt the text/logic of store procedure, view and function.

Encrypt store procedure

Syntax

create proc sp with encryption

as begin

Select * from tablename

end

Example:

create proc Spuser with encryption

as begin 

Select * from dbo.tbuser 

end

Encrypt view

create view fnuser with encryption

as

Select * from tbuser

Encrypt function

CREATE FUNCTION udfnuse (@userID int)  

RETURNS int   with encryption

AS

BEGIN 

   return @userid

END; 

GO 

Note: if store procedure, view and function are encrypted it is not possible to get definition. We can get the definition of encrypted store procedures, view and function using the third party tools (Optillect SQL Decryptor).