Stored Procedures are very important and most of the business logic of my applications are always coded in Stored Procedures. Sometime it is necessary to hide the business logic from end user due to security reasons or any other reason. Keyword WITH ENCRYPTION is used to encrypt the text of the Stored Procedure. One SP are encrypted it is not possible to get original text of the SP from SP itself. User who created SP will need to save the text to be used to create SP somewhere safe to reuse it again.
Interesting observation:
What prompted me to write this article is when SP are encrypted Actual Execution Plan of the SP is also not visible. In fact, it does not respond to that command and does not display the tab of execution plan if only Encrypted SP is called.
To understand this scenario let us create two SPs. First without encryption and second with encryption. Once SPs are created run them one by one with having Actual Execution Plan turned on. To learn how to turn on Actual Execution Plan read my previous article SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan.
/* Create SP without Encryption */
CREATE PROCEDURE #RegularSP
AS
SELECT TOP 10 City
FROM Person.Address
GO
/* Create SP with Encryption */
CREATE PROCEDURE #EncryptSP
WITH ENCRYPTION
AS
SELECT TOP 10 City
FROM Person.Address
GO
/* Execute SP - Execution Plan Tab shows up */
EXEC #RegularSP
GO
/* Execute SP - Execution Plan Tab does not shows up */
EXEC #EncryptSP
GO
/* Clean Up */
DROP PROCEDURE #RegularSP
DROP PROCEDURE #EncryptSP
GO
It is very clear from above example that Encrypted SP does not show the actual execution plan as from Actual Execution Plan user can figure out the logic behind the SP. To avoid this issue for encrypted SP Management Studio does not display the execution plan.
Reference : Pinal Dave (http://www.SQLAuthority.com)
20 Comments. Leave new
thanks for the info and for keeping your blog interesting for long time.
now what reason do i have to encrypt sp’s or better to say hide an execution plan?
Pinal,
Thanks for this Tip.
Worth noting is, this concept works only with SQL Server 2005. I tried doing this in SQL Server 2000. Even if you include With encryption in your stored proc, in SQL Server 2000 you will be able to see the execution plan. Since you did not mention this in your article I thought it will work in both.
@mdma
Main Idea behind using with encryption is to hide the SQL Code, not the execution plan. Like Pinal mentioned in his article, many business logics are written in stored procedures which companies do not want others to know. This is the case especially with COTS products.
In SQL Server 2005 SSMS ( Object explorer ), you can see a Lock symbol on stored procedure name indicating this stored procedure is encrypted.
Thanks,
Imran.
Hi Pinal,
I would like to know the performance impact of using temp tables in stored procedures. I believe temp table cause SP recompilation.
I would like to why and when this causes the stored procudre to recompile? I would also like to know the improvement in SQL 2005.
Thank you.
Hi All,
There are decrypters available in Net to decrypt all our Encrypted SPs..So how to make our logic protected from those decrypters..we have to use our own encription algo (:'( ) or any loop holes are there ?? any idea?
Hi,
I am having about 1000 store procedure and view.
Is there any way to generate store procedure and view objects encrypted script?
Thx in advance
Vinay K
Where are these SPs stored? I created them in the AdventureWorks db and was able to run them, however when I looked in the AdventureWorks\Programmability\Stored Procedures directory, neither SP was there.
Also, what are your thoughts on creating an encrypted SP that queries encrypted data? Is this a good/bad way to keep the keys and certificate hidden?
Thanks for your help.
Try right clicking the Stored Procedures folder and select “Refresh”
Well these stored procs in this example were created with a hash in the front of the name, therefore they are TEMP stored procs, just like temp tables. That’s why you can’s see them in the Stored Procedures list on the server
Hi,
I would like add one more question here how SQL server internally handle this ?
what are the performance constraint ?
although i found many articles that it is not safe enough it can easily be decrypted so is there any other way?
Thanks
Rajat
Hi,
Good example, But if i know the SP name(EncryptSP) . Use this name, I can able to modify the SP. So, What is the use of ENCRYPTION.
As pleitch already states – it is possible to decrypt sprocs. If you have a few you don’t need a special tool. Instead google “dac decrypt sprocs”.
A strong word of caution:
We’ve moved away from decrypting sprocs.
Search for “Now to the not so good sides” on:
https://blog.sqlauthority.com/2006/12/10/sql-server-find-stored-procedure-related-to-table-in-database-search-in-all-stored-procedure/
to understand why.
and of course “We’ve moved away from decrypting sprocs.” should be We’ve moved away from ENCRYPTED sprocs.”
Thank you Pinal, i like you blog. Your posts are so helpful and to the point.
How to decrypting the stored procedure .
How to decrypting the stored procedure .
how to encrypt parameterised stored procedure
How can only password encrypt and decrypt in sql server?
Thank you for this very helipfull advise.
Would like to know how to encrypt a SP to a specific database on not the default tempdb.
EG
I have db called MISER
How do I create a SP to this specific db, for the SP to sit in the Programmability /SP
Tx
Neels
How to decrypting the stored procedure .
after created with encryption
Please help me
Just for the Note: Recently I have tried to migrate a database with Sql server 2016 from on premises to Azure SQL Server. All store procedures written with WITH ENCRYPTION are blocked by Azure DMA (data migration assessment tool).
I only able to migrate once completely removed the syntax from all store procedures.