SQL SERVER – Hide Code in SSMS

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

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.