What Is the Purpose of WITH ENCRYPTION?

  • slayrexx

    Valued Member

    Points: 68

    Can someone please help me understand the usefulness of encrypting a stored procedure?

  • Adam Angelini

    SSCrazy Eights

    Points: 8538

    I see this mostly used in "packaged" applications that have a SQL backend. It's so you can't use the logic in their sprocs. I have rarely seen this used in "in house" applications

  • slayrexx

    Valued Member

    Points: 68

    Thanks for that. To recap, it makes sense to encrypt client or distributed object, not necessarily objects that will remain on a central server, even a serve to which those clients my connect?

  • Adam Angelini

    SSCrazy Eights

    Points: 8538

    If you want to hide the text from anyone who has access to view the object definition, that is when you will want to use encryption.

  • GRE (Gethyn Ellis)

    SSCrazy Eights

    Points: 9486

    so i fyou have written your own appication then you may want to encrypt your SP's etc so your customers cannot see the underlying code...

    Gethyn Ellis
    www.gethynellis.com

  • Vijaya Kadiyala

    SSCrazy Eights

    Points: 9119

    Hi

    At times, it is needed that you encrypt the text of stored procedures containing sensitive information. SQL Server provides the WITH ENCRYPTION to encrypt the text of the stored procedure.

    CREATE procedure [dbo].[Ten Most Expensive Products Encyrpt]

    WITH ENCRYPTION AS

    SET ROWCOUNT 10

    SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice

    FROM Products

    ORDER BY Products.UnitPrice DESC

    Once the stored procedure has been created WITH ENCRYPTION, attempts to view the stored procedure returns a message specifying that the text is encrypted:

    EXEC sp_helptext usp_SEL_EmployeePayHistory

    'The text for object 'Ten Most Expensive Products Encyrpt' is encrypted.'

    One note of caution. Save the original text of the stored procedure before encrypting it, as there is no straightforward way to decode the encrypted text. One hack is to attach a debugger to the server process and retrieve the decrypted procedure from memory at runtime.

    Check out the below link

    http://www.sqlservercurry.com/2008/02/how-to-encrypt-stored-procedure-in-sql.html

    Thanks -- Vj

    http://dotnetvj.blogspot.com

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply