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