Jul 12, 2013

Encrypt Stored Procedure

In SQL Server, stored procedures are generally created to hold the business logic of the application. For security reasons, you may want to hide your business logic from the end users.

With Encryption keyword is used to encrypt the definition of the stored procedure.

NOTE: Once stored procedure is encrypted it is not possible to get the definition of the stored procedure.

Before encrypting, developers have to save the original definition as a backup as it will be referred in case you need to check the business logic or in case any modification is required in the business logic.

For an example, Let's create an encrypted stored procedure using With Encryption keyword
create procedure encryptedProc with encryption
as
begin
    select 1
end

Now let's try to view the text/definition of stored procedure
sp_helptext encryptedProc
OUTPUT

    Choose :
  • OR
  • To comment
2 comments:
Write Comments
  1. How can I view the stored procedure text than?

    ReplyDelete
    Replies
    1. At developer level, you can't do with SSMS. However at admin level it can done using dedicated administration connection with SSMS or using third party tools.

      Delete