Hide Procedure Code in SSMS: What WITH ENCRYPTION Covers

To hide procedure code from the people who use your database, create the procedure WITH ENCRYPTION. The clause blocks the usual ways to read the text. It does not make the code secret, and it costs you something too. A small demo measures both sides.

Create One Plain and One Hidden Procedure

The demo creates a database named ProcHideDemo with a small sales table and two procedures that run the same query. One is plain. The other carries the WITH ENCRYPTION clause after the procedure name. Run it on a test server.

IF DB_ID(N'ProcHideDemo') IS NULL CREATE DATABASE ProcHideDemo;
GO
USE ProcHideDemo;
GO
DROP PROCEDURE IF EXISTS dbo.PlainReport, dbo.HiddenReport;
DROP TABLE IF EXISTS dbo.Sales;
CREATE TABLE dbo.Sales (SaleID int NOT NULL PRIMARY KEY, Amount decimal(10,2) NOT NULL);
INSERT INTO dbo.Sales (SaleID, Amount) VALUES (1, 10.50), (2, 20.00), (3, 7.25);
GO
CREATE PROCEDURE dbo.PlainReport
AS
SELECT COUNT(*) AS Sales, SUM(Amount) AS Total FROM dbo.Sales;
GO
CREATE PROCEDURE dbo.HiddenReport
WITH ENCRYPTION
AS
SELECT COUNT(*) AS Sales, SUM(Amount) AS Total FROM dbo.Sales;

What the Clause Hides

The classic way to read a procedure in Management Studio is sp_helptext. The first call returns the full text of the plain procedure. The second call prints one line, shown below the code.

EXEC sp_helptext N'dbo.PlainReport';
EXEC sp_helptext N'dbo.HiddenReport';

The text for object 'dbo.HiddenReport' is encrypted.

The catalog view that stores the text holds nothing for the hidden procedure. The next query shows the definition column of both.

SELECT OBJECT_NAME(m.object_id) AS ProcName, m.definition
FROM sys.sql_modules AS m
WHERE m.object_id IN (OBJECT_ID(N'dbo.PlainReport'), OBJECT_ID(N'dbo.HiddenReport'))
ORDER BY ProcName;

The function OBJECT_DEFINITION returns NULL for the hidden procedure in the same way. A user who can open Object Explorer sees the procedure but cannot read it.

The code is hidden, but its footprint is not. The dependency view still lists every object that the hidden procedure reads. Anyone with metadata access can see which tables it touches.

SELECT OBJECT_NAME(d.referencing_id) AS ProcName, d.referenced_entity_name AS ReadsTable
FROM sys.sql_expression_dependencies AS d
WHERE d.referencing_id = OBJECT_ID(N'dbo.HiddenReport');

What It Costs You

The hiding has a price. SQL Server also keeps the text and the plan of the procedure away from the tools that tune queries. The next script runs both procedures, then asks the plan cache for the text and the plan of each.

EXEC dbo.PlainReport;
EXEC dbo.HiddenReport;
SELECT OBJECT_NAME(ps.object_id) AS ProcName,
       CASE WHEN st.text IS NULL THEN 'no text' ELSE 'text' END AS SqlText,
       CASE WHEN qp.query_plan IS NULL THEN 'no plan' ELSE 'plan' END AS PlanXml
FROM sys.dm_exec_procedure_stats AS ps
OUTER APPLY sys.dm_exec_sql_text(ps.sql_handle) AS st
OUTER APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp
WHERE ps.database_id = DB_ID()
ORDER BY ProcName;

For the hidden procedure there is no text and no plan. In Management Studio, the actual plan of an encrypted procedure does not appear either. A slow hidden procedure is harder to diagnose, for you and for anyone you hand it to.

Find Every Hidden Module

A database can hold hidden modules that nobody remembers. Procedures, functions, views and triggers can all carry the clause. The query below lists every module of the database that has no stored definition. The filter on shipped objects keeps out Microsoft modules. Run the query with VIEW DEFINITION on the database, because a module you cannot read shows a NULL definition too.

SELECT s.name AS SchemaName, o.name AS ObjectName, o.type_desc AS ObjectType
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE m.definition IS NULL AND o.is_ms_shipped = 0;

The Trap: ALTER Without the Clause

The clause belongs to the CREATE or ALTER statement, not to the object. An ALTER that leaves it out stores the text in plain form again. A deployment script that rebuilds the procedure without the clause quietly unhides it. The script below does exactly that, reads the text, and then puts the clause back.

ALTER PROCEDURE dbo.HiddenReport
AS
SELECT COUNT(*) AS Sales, SUM(Amount) AS Total FROM dbo.Sales;
GO
EXEC sp_helptext N'dbo.HiddenReport';
GO
CREATE OR ALTER PROCEDURE dbo.HiddenReport
WITH ENCRYPTION
AS
SELECT COUNT(*) AS Sales, SUM(Amount) AS Total FROM dbo.Sales;

The sp_helptext call now returns the full text, although the procedure was hidden a moment ago. Write the clause into every ALTER of a hidden procedure. Then run the query that lists modules without a definition.

Hiding Is Not Security

The encryption is weak by design. Microsoft documents it as obfuscation, and a person with sysadmin rights and a dedicated administrator connection can read the text. Anyone who gets the database files or a backup can try too. If a vendor needs to protect its logic, a license and a contract do more than this clause. If you need to protect data, use real encryption of the data.

The first step to hide procedure code is a permission, not a clause. A database user who has only EXECUTE on a procedure can run it and cannot read it. The text becomes visible with VIEW DEFINITION, or with a permission that includes it, such as ALTER or CONTROL. The script below tests a user with each permission.

IF USER_ID(N'ReportReader') IS NULL CREATE USER ReportReader WITHOUT LOGIN;
GRANT EXECUTE ON dbo.PlainReport TO ReportReader;
EXECUTE AS USER = N'ReportReader';
SELECT CASE WHEN OBJECT_DEFINITION(OBJECT_ID(N'dbo.PlainReport')) IS NULL THEN 'no text' ELSE 'text' END AS WithExecuteOnly;
REVERT;
GRANT VIEW DEFINITION ON dbo.PlainReport TO ReportReader;
EXECUTE AS USER = N'ReportReader';
SELECT CASE WHEN OBJECT_DEFINITION(OBJECT_ID(N'dbo.PlainReport')) IS NULL THEN 'no text' ELSE 'text' END AS WithViewDefinition;
REVERT;

This costs nothing and keeps the plan and the text for you. Use it before you reach for the clause.

Keep the source in a place you control. Without its source, a hidden procedure cannot be changed. Hide procedure code only when the source lives in a safe place. The clause applies to procedures, functions, views and triggers. It does not apply to tables.

Should You Hide Code at All?

You could argue that a vendor has every right to protect its work. It does, and customers still need to tune their servers. I do not like hiding code in a database I help to tune, because I cannot read what runs. When a client asks for it, I explain the cost first and then do it.

What to Remember

To hide procedure code, add WITH ENCRYPTION to the CREATE and to every later ALTER. The text, the definition and the plan are then out of reach of normal tools. It is obfuscation, not a safe. Keep your source elsewhere, and list the hidden modules now and then.

When you finish, run the cleanup script. It removes the demo database.

USE master;
GO
ALTER DATABASE ProcHideDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ProcHideDemo;

Hidden code is not secret code, it is code that is harder to read.

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