SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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.

SQL stored procedure encryption on SQL Server
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.

sp_helptext with encrypted SQL stored procedure

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.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems