SQL SERVER – Explanation of WITH ENCRYPTION clause for Stored Procedure and User Defined Functions

This article is written to answer following two questions I have received in last one week.

Questions 1) How to hide code of my Stored Procedure that no one can see it? 2) Our DBA has left the job and one of the function which retrieves important information is encrypted, how can we decrypt it and find original code?

Answers 1) Use WITH ENCRYPTION while creating Stored Procedure or User Defined Function. 2) Sorry, unfortunately there is no simple way to decrypt the code. Hard way is too hard to even attempt.

Explanations of WITH ENCRYPTION clause If SP or UDF are created WITH ENCRYPTION it is one way street and it is not possible to decrypt it using SQL Server commands. It is always advised to save a copy of the script used to create the SP or UDF on other media than SQL Server. There is no way to get the original source code once it is executed on Server. Only privileged users who can access system tables over the DAC port or directly access database files as well as can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime.

CLR SP and UDF can not be encrypted. SQL Server replication can not replicate encrypted SP or UDF.

I believe in fair programming techniques and strongly recommend against encryption. There should be no need to encrypt any code. If there is need to hide any code from certain users in that case user should be restricted using user login permissions.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Fix : Error : Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)
Next Post
SQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC

Related Posts

37 Comments. Leave new

  • Optillect Team
    October 31, 2010 3:55 pm

    Well, it’s pretty easy. Run Optillect SQL Decryptor Freeware, decrypt a stored procedure you need, and copy the DDL script. Then run Microsoft SQL Server Management Studio, create new SQL editor with the same connection, paste the DDL script from the clipboard. Now remove “WITH ENCRYPTION” option, replace “CREATE” keyword with “ALTER” and execute the script. Thus you’ll get your stored procedure stored with no ecryption.
    Unfortunately, SQL Decryptor v1.1 cannot do it automatically at the moment, but we are planning to support this feature in the next release.

    Reply
  • hi

    i was working on SQL SERVER DBA

    i faced one problem from ENCRYPTED STORED PROCEDURES

    HOW TO RETRIEVE AND HOW TO READ THE ENCRYPTED STORED PROCEDURE FROM SQL DATABASE

    THANK U

    Reply
  • I have onlin exam how i do encrypt /decrypt database files or tables like ( question , answer . and result …etc) please can you explenation me step by step

    thanks

    Reply
  • @Naga, does SQL Decryptor Freeware meet your needs?

    Reply
  • is there any to get the SP main query part only…for example

    create proc getinfo
    (
    @id int
    ) select *from infotable where id=@id

    we can the sp parametres and data type with help of sys tables…but how i can get the sp main script part i.e. select *from infotable where id=@id only???

    Reply
  • is there any tool for sp encryption??

    Reply
  • Update Optillect decrypter was able to decrypt the SQL server 2008, 2008r2,

    encrypted

    when I tried to use on SQL server 2012 RC0 it is giving me an error

    Error : No Object definition found

    I guess the SQL team might have changed the encryption algorithm for the new

    sql server 2012

    Reply
  • How to write in sp wth in sql queries

    Reply
  • Robert Stewart
    March 23, 2012 11:31 pm

    akhil393,
    Another update, Optillect’s web site is not available any more. Would it be possible fou you to send me the tool?

    Reply
  • Hi
    As you said that “With Encryption” cannot be decrypted when i use alter command to modify sp without using “With Encryption” in that alter command then definitely it will remove the encryption for that sp.
    This may cause Readable of stored procedure…
    The code which i used for Encryption is

    create proc proc123
    With Encryption
    As
    Begin
    select Title as [Name of Song],director as [Director] from videos
    End
    Go
    —–Encryption is done here—-
    *Alter proc proc123

    As Begin
    select * from videos
    End
    Go
    *
    —–Returned
    Please give me another way to use the encryption for UDF and SP’s.

    Reply
  • Hi
    I have few views which is encrypted in my database , is there any way to open and alter the view. or I should Drop and recreate the view,
    Pls Do Help me in this

    Reply
  • but we can not encrypted any stored procedure (if parameterised) plz suggest me if is it possible

    Reply
  • Hello Pinal,

    We are hosting our DB at our client’s machine in their premise. They have full access of the database, which we cannot stop. In this case, can we hide SP code and trigger code?
    Thanks,

    Reply
  • Rasik Bihari Tiwari
    August 3, 2017 3:15 pm

    Great article as usual. As you have mentioned in the last paragraph of your article “any code from certain users in that case user should be restricted using user login permissions” – Is there any granular level of security access rights in SQL Server which can give access to a user to query database or execute a stored procedure but not view its contents. If I give a user only “public” server role then he fails to fire even a select query on the database. The moment I make him sysadmin then he can execute and modify a stored procedure. How would someone implement the intermediary level access rights where one can just execute an SP but not modify or view its contents.

    Reply

Leave a Reply

Menu