Today we are going to learn something very simple but asked a lot by all of you as well as my client is that how to hide code in SSMS. Well, let us learn it in today’s blog post. I often discuss this during my  Comprehensive Database Performance Health Check.
There are many reasons why one wants to hide the code in SSMS. When you are a third-party vendor and do not want your client to get access to the code or want them to modify such a way that it may not work as intended, you may want to use this method.
If you ask me personally, I never like to hide code. However, when clients ask you for help, we all have to do it. Remember – customers are always right.
Hide Code in SSMS
Let us first create the following stored procedure.
CREATE PROC SimpleSP AS SELECT TOP 100 * FROM Application.People p INNER JOIN Sales.InvoiceLines il ON p.PersonID = il.LastEditedBy INNER JOIN Warehouse.StockItemTransactions sit ON p.PersonID = sit.LastEditedBy ORDER BY il.StockItemID GO
Now enable the execution plan and run the stored procedure with the following code.
EXEC SimpleSP
You will be able to see the stored procedure results and execution plan as well. Additionally, when you try to see the text of the stored procedure with
sp_helptext SimpleSP
You will see the following text in the resultset.
Encrypted SP
Now create the following stored procedure with the encrypted keywords.
CREATE PROC EncryptedSP WITH ENCRYPTION AS SELECT TOP 100 * FROM Application.People p INNER JOIN Sales.InvoiceLines il ON p.PersonID = il.LastEditedBy INNER JOIN Warehouse.StockItemTransactions sit ON p.PersonID = sit.LastEditedBy ORDER BY il.StockItemID GO
Now enable the execution plan and run the stored procedure with the following code.
EXEC EncryptedSP
You will be able to see the stored procedure results but you will not see the execution plan tab at all.
When you try to see the code in the following command, you will not see anything at all.
sp_helptext EncryptedSP
The text for object ‘EncryptedSP’ is encrypted.
Well, this is how you can hide your code in SQL Server Management Studio (SSMS).
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
Pinal, have you a cut and paste error in this blog?
After the user creates an SP WITH ENCRYPTION, you invoke it with EXEC SimpleSP. Should not that be EncryptedSP?
You are correct about it. It is copy paste error and I fixed it.