Options for hiding SQL Server code

By:   |   Comments (4)   |   Related: > Security


I am required to restrict the access to some users to view the definition of specific SQL Server database objects. I am aware of the WITH ENCRYPTION clause that is used to encrypt the code for objects like views, stored procedures and functions. It sort of fulfills my purpose and is easy to implement, but it does not cover all SQL Server objects.  In this tip we look at another option to restrict users from viewing the definition/code of any SQL Server object.


The WITH ENCRYPTION clause is used to encrypt the definition of SQL Server objects (views, stored procedures, functions ), so that no one may be able to view the definition/code of that specific object. Using the clause while creating an object is fairly easy, but there are some serious points and possible complications.

Before discussing the complications, let us go through the simple use of this clause for defining a view, stored procedure and UDF.

Creating encrypted view SP UDF

The above objects have been created and can be seen in SSMS and also can be verified from table sys.sysobjects. If you try to generate a CREATE or ALTER script for objects that were encrypted using the WITH ENCRYPTION option then you will get the following error, because this task involves viewing and using the object code.

Error while generating CREATE or ALTER

Also, if sp_helptext is used to view the definition of any object encrypted using the WITH ENCRYPTION clause then the following error will be generated

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

Although using the WITH ENCRYPTION option seems straight forward to hide the definition of any view, stored procedure or function there are two main problems with this option:

  1. The definition/code of object will not be available to anyone regardless of their permissions or access level. You can not customize the permissions for code/definition visibility.
  2. Once an object has been created on the server using the WITH ENCRYPTION option, there is no standard method provided to get the definition/code back out of the encrypted object. You will need to keep a copy of the code outside of SQL Server.

Keeping in mind the above mentioned complications, using the WITH ENCRYPTION option becomes risky. Code for these objects may be required at a later time for consultation or alteration. So it would be imperative to save the code in some other place where it can safely be accessed later.


Using WITH ENCRYPTION is not a recommended best practice to hide the definition/code of an object. Luckily, there is an alternative approach for SQL Server.

If it is required to hide the definition/code of any object from a user then standard permissions can be used for this purpose. This can be done by granting or revoking View Definition rights. 

If permission View Definition is denied for an object to any user then the user would not be able to view the object in SSMS or view its code through the system stored procedure sp_helptext.

The View Definition permission may also be used for other objects in SQL Server like tables, synonyms etc. View Definition permissions can be granted or denied using both T-SQL or SSMS

Script to deny VIEW DEFINITION permission
-- Syntax to use VIEW DEFINITION Permission
--To deny VIEW DEFINITION permission to User1 on HumanResources.vEmployee
USE AdventureWorks
DENY VIEW DEFINITION ON [HumanResources].[vEmployee] TO User1

For implementation of permissions through SSMS

  • Right click on the object that is required to hide the definition
  • Click on Properties, a frame will appear, select grant or deny View Definition permission on the object for the selected user or role

Implement VIEW DEFINITION permission through SSMS

Denying View Definition permission will hide the object for a specific user and also the user will not be able to see the definition using sp_helptext.

Other permissions like SELECT, INSERT etc will remain intact.

The permissions are flexible and can be implemented on the following four levels:

  • Server level. At server level this permission will be listed as View Any Definition
  • Database level
  • Schema level
  • Individual entity level
Next Steps
  • Avoid using the WITH ENCRYPTION option other than for very specified requirements. It may lead to administrative problems later.
  • Using standard permissions is much more flexible and easy to manage.
  • Click here to read further about VIEW DEFINITION permission in BOL.
  • Bind your related users to legal copyright contracts for change prevention whenever required.
  • WITH ENCRYPTION option can not be used for CLR objects
  • When taking over any new database make sure that you have backup code for all encrypted objects. The following script can be used to identify encrypted objects in a database.
Script to get list of encrypted objects in SQL Server database
--Get list of encrypted objects in a database
Use DatabaseName
SELECT OBJECT_NAME(id) as ObjectName
FROM sys.syscomments WHERE encrypted = 1

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Saturday, August 3, 2013 - 6:36:36 AM - Uday Back To Top (26101)

Many many thanks, ice article.

Sunday, June 13, 2010 - 9:38:03 PM - Paulie-D Back To Top (5695)

 The article fails to mention that you can engage encryption on an already-existing Stored Procedure (etc):

ALTER  procedure [owner].[sp_YourExistingStoredProcedure]


Some other related articles of interest:





Tuesday, September 8, 2009 - 4:36:40 AM - ALZDBA Back To Top (4011)

Just hopping in ...

Off course ... sysadmins are the exception !

Very early in the authentication process, a member of SQL sysadmins is being switched to the SQL-upper-being, friom that point on some checks are nolonger relavant.

So, in the case where you don't want sysadmins to be able to view your ddl, you'll need to reside to using "with encryption".
However, even that can be workedaround. (I didn't test that on sql2005/2008).

IMO the same goes for CLR procedures. 


Tuesday, September 8, 2009 - 3:44:05 AM - dale.turley Back To Top (4010)

Thanks, this is a good idea. The only problem i face with this is;

 I have several stored procedures that use functions / algorithms to encrypt / mashup data - i created these stored procedures using 'with encryption' and no-one can see the inner workings of ho they are encrypted.

 I tried what you said in this article but members of sysadmin server role can still see the definition even if i have explicitly denied them VIEW DEFINITION!

Have i set this up wrong or is this 'by design'??

get free sql tips
agree to terms