Encrypt Stored Procedure, View, Function on SQL Server
SQL programmers may require to encrypt SQL source code of stored procedures, SQL views, user defined function in their databases to prevent the sensitive data structures easily be obtained by unauthorized users. Sometimes even database administrators encrypt SQL objects so that the SQL Server developers will not be able to see the code running within that SQL stored procedure or user defined function, etc.
It is also a method to use Ad-Hoc distributed queries in SQL objects like database views or stored procedures where remote database connection strings and username password pairs are stored within the SQL object. In such cases unless you cannot avoid this way, to increase security precautions it is good to encrypt database SQL view or stored procedure using With Encryption option as shown in this SQL tutorial.
How to Encrypt Stored Procedure Code
To encrypt a stored procedure on SQL Server database is very simple. All to do is to add ENCRYPTION as procedure option during CREATE PROCEDURE command in WITH clause.
Here is a sample stored procedure created with Encrypted option is enabled.
Create Encrypted Stored Procedure on SQL Server
use [Kodyaz.Development]
GO
Create Procedure Finance.SampleEncryptedSQLStoredProcedure
With Encryption
AS
Select employeeId, period, paymentDate, PaymentAmount, note
From Finance.MonthlyPayment
GO
Please note that, normally it is not possible to decrypt back to its original source code from an ecrypted stored procedure. So it is my strongly advice for database administrator and SQL developers to keep the source codes of their encrypted database objects in case they need a modification on their source codes.
For example, on your development environment SQL developers in your team can work with un-encrypted versions of the stored procedures. And when the stored procedures are deployed to test or productive databases, these objects can be encrypted and executed with their encrypted versions.
How to Encrypt User Defined Function
If you want to to encrypt a user defined function in your SQL database, again the WITH ENCRYPTION clause will help you in case you use it in CREATE FUNCTION or in ALTER FUNCTION command.
Here is a sample encrypted user function create script.
create function Finance.CalculateSalary (
@employeeId int
) returns Decimal(18,3)
with encryption
as
begin
declare @Salary decimal(18,3)
Select @Salary = PaymentAmount
From Finance.MonthlyPayment
Where employeeId = @employeeId
return @Salary
end
Encrypt SQL Views in SQL Server Database
In order to encrypt a SQL Server database view, it is enough to add the With Encryption option during Create View or Alter View statements as seen in below SQL View sample.
create view Finance.SalaryList with encryption
as
with cte as (
Select max(period) period From Finance.MonthlyPayment
)
Select p.*
From Finance.MonthlyPayment p
Inner Join cte on p.period = cte.period
In fact the source code and the SQL query does not change, but the CREATE & ALTER statements change with addition of WITH ENCRYPTION option.
sp_helptext will Not Display Encrypted Stored Procedure Codes
When a stored procedure is encrypted in an SQL Server database, sp_helptext system stored procedure will not display the SQL source codes of the encrypted procedure.
Let's try running sp_helptext to show source of our sample encrypted stored procedure.
sp_helptext "Finance.SampleEncryptedSQLStoredProcedure"
SQL Server engine will respond as follows:
The text for object 'Finance.SampleEncryptedSQLStoredProcedure' is encrypted.
So keeping in mind that the text for encrypted objects like stored procedures or user functions will not be displayed by simply running the sp_helptext system procedure with the name of the encrypted procedure as an argument.
One more it is a best practise to keep the source codes of SQL Server database objects like encrypted stored procedures in an other environment in case you need them.
Please check following referred articles and SQL tutorials for more sample cases on Ad Hoc distributed queries within SQL Server encrypted stored procedures and create encrypted views with remote database connection and limitations on search text in SQL Server database objects.