Post reply

Recompile procedure to encrypt

  • February 20, 2009 at 7:49 am

    #131090

    Hi,

    In order to prevent modifications to a stored procedure in a production environment (and forgetting to modify the source in the development environment), my intention is to encrypt those SPs

    But I don't want to include the "with encryption" statement in the sourcecode (i have one sourcecode file which may be altered in test)

    Is this possible (step 4):

    1) develop a SP in test (without "with encryption")

    2) export this script to a file, store it in a safe place/versioning system

    3) run this script in production

    4) execute a recompile statement in production for this SP with the encryption flag on

    Thanks!

    Wilfred
    The best things in life are the simple things

  • February 20, 2009 at 8:07 am

    #947532

    As far as I know, the only way to encrypt a proc is to do an ALTER PROCEDURE.

    ALTER PROCEDURE <Procedure Name>

    WITH ENCRYPTION

    AS

    ... rest of procedure here ...

    GO

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 20, 2009 at 8:24 am

    #947549

    Yep, and that's not what I want.

    Btw nice (new) picture of you, I didn't know you had a beard 🙂 😀 😛

    Wilfred
    The best things in life are the simple things

  • February 20, 2009 at 8:40 am

    #947566

    Wilfred van Dijk (2/20/2009)

    Btw nice (new) picture of you, I didn't know you had a beard

    😛

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 26, 2009 at 2:46 pm

    #967060

    After reading some articles about SQLCmd, I found a solution by setting a variable to either "with encryption" or a blank space. It's something like this:

    prod.sql:

    :setvar encrypt "with encryption"

    :r c:\temp\myproc.sql

    myproc.sql:

    create procedure usp_myproc

    $(encrypt)

    as

    print 'hello world"

    go

    development.sql:

    :setvar encrypt " "

    :r c:\temp\myproc.sql

    Wilfred
    The best things in life are the simple things

Viewing 5 posts - 1 through 4 (of 4 total)

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