Sunday, January 8, 2017

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.


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). 



No comments:

Post a Comment