Saturday, October 14, 2017

How to Protect the Code in View or a Stored Procedure with Encryption in SQL Server

Protecting the Code in View or a Stored Procedure with Encryption in SQL Server
If you wants to Protect(Not able to view) your Source Code of your View/Stored Procedure in SQL Server, you can do it by declaring the "With Encryption" option as explained below.

Please note that, Once you Encrypted the Code "With Encryption" option then there is no way to get it back. Make sure that you stored your source code somewhere for future reference, otherwise you have to re-write the code from scratch.

Creating a View with Encryption :
Create View EncVw_EmpDtls
WITH ENCRYPTION
As
SELECT [Emp_Id],[Emp_Name],[Gender],[Dept_Id],[Basic_Sal]
from [dbo].[Emp_Temp1] Where [Emp_Id]<2345700

GO
--------------------------------------------------
Creating a Stored Procedure with Encryption :
Create Procedure [dbo].[SP_Emp_ByDept_Details]                        
    (
  --Parameter Declaration                          
     @Dept1 as varchar(255) = NULL                   
    ) 
WITH ENCRYPTION                         
  AS 

 --Variables Declaration 
   DECLARE @Get_Dept AS varchar(255)=NULL,
     @SQL_Stmnt AS varchar(255)=NULL 


 BEGIN

   TRUNCATE TABLE Emp_Dtls_ByDept
 --Defining Variable with Where Cluase statement by passing Parameter as value
   SET @Get_Dept =  'WHERE [Dept_Name] ='''+ @Dept1 + ''''
   Print ( @Get_Dept ) 

 --Performing an Operation
   SET @SQL_Stmnt = 'INSERT INTO Emp_Dtls_ByDept SELECT * FROM EMP_Details ' + @Get_Dept
   Print ( @SQL_Stmnt )
   EXEC ( @SQL_Stmnt)

   SELECT*FROM Emp_Dtls_ByDept 
END
--------------------------------------------------
Now if you Try to view the Source Code of your View/SP..
sp_helptext EncVw_EmpDtls
GO
sp_helptext SP_Emp_ByDept_Details
GO

then SQL Server will throw the following error messages :
The text for object 'EncVw_EmpDtls' is encrypted.
The text for object 'SP_Emp_ByDept_Details' is encrypted.


-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog