Options for hiding SQL Server code

By: Atif Shehzad   |   Updated: 2009-09-01   |   Comments (4)   |   Related: > Security

Problem

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.

Solution

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.

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.

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.

ALTERNATE TO USING WITH ENCRYPTION CLAUSE

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

DENY/GRANT/REVOKE VIEW DEFINITION ON OBJECTNAME TO USERNAME
GO
--To deny VIEW DEFINITION permission to User1 on HumanResources.vEmployee
USE AdventureWorks
GO

DENY VIEW DEFINITION ON [HumanResources].[vEmployee] TO User1
GO

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

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
GO
 
SELECT OBJECT_NAME(id) as ObjectName
FROM sys.syscomments WHERE encrypted = 1
GO

About the author

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