Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Options for hiding SQL Server code


By:   |   Read Comments (4)   |   Related Tips: More > Security

Attend a SQL Server Conference for FREE >> click to learn more


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


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, August 03, 2013 - 6:36:36 AM - Uday Back To Top

Many many thanks, ice article.


Sunday, June 13, 2010 - 9:38:03 PM - Paulie-D Back To Top
[quote]http://www.mssqltips.com/tip.asp?tip=1828[/quote] 

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

ALTER  procedure [owner].[sp_YourExistingStoredProcedure]
WITH ENCRYPTION
as
begin

 

Some other related articles of interest:

http://databases.aspfaq.com/database/how-do-i-protect-my-stored-procedure-code.html

http://blog.sqlauthority.com/2007/07/01/sql-server-explanation-of-with-encryption-clause-for-stored-procedure-and-user-defined-functions/


ENCRYPTING A COLUMN:
http://www.mssqltips.com/tip.asp?tip=928

http://msdn.microsoft.com/en-us/library/ms179331.aspx


Tuesday, September 08, 2009 - 4:36:40 AM - ALZDBA Back To Top

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 08, 2009 - 3:44:05 AM - dale.turley Back To Top

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'??


Learn more about SQL Server tools