Post reply

Encrypt All Stored procedures at once

  • November 26, 2008 at 2:03 am

    #215880

    Comments posted to this topic are about the item Encrypt All Stored procedures at once

  • September 10, 2009 at 8:53 am

    #1051758

    how to Dcrypt it...

    Regards,
    Shivrudra W

  • December 15, 2009 at 4:09 am

    #1092423

    worked well on some stored procs but missed some for no real reason that I can see.

    Very handy non the less.

    Thanks

  • May 10, 2010 at 9:22 am

    #1165060

    I think this code is prone to errors. If 'AS' is part of the parameter's name, for example.

  • February 8, 2011 at 1:32 pm

    #1284024

    you can't decrypt it, but if you follow the instructions there should be a backup up of your sp's in the database...

  • July 20, 2011 at 8:33 pm

    #1356116

    There are a couple of issues I found with the script Chris.

    1- If there was no BEGIN after the AS at the beginning of the SP then the WITH ENCRYPTION syntax could not be inserted into the text of the SP

    2 - Because the way syscomments stores the text of the SP's (by spreading the text over multiple rows of the system view when the text of the SP is over a certain length), that when trying to join all the text back together, some text is truncated because of the insertion of more text when inserting 'WITH ENCRYPTION', this then breaks the SP therefore when trying to alter it, by running EXEC (@sptext) it fails and is therefore caught by the CATCH statement which says

    the stored procedure 'SPNAME' cannot be encrypted automatically.

    You will find any SP that is over 'apprx' 200 lines, that your script wont automatically encrypt them. Also if there is no BEGIN after the AS statement, that the script will also fail for that SP.

    i.e.

    ALTER Procedure [dbo].[BCPReplicationIn]

    AS

    begin <-- If this BEGIN doesn't exist the script wont insert WITH ENCRYPTION before the AS.

    I was trying to get your script to work, but it seems like a hard task mainly because of the way sys.syscomments stores the text of the SP's. Hopefully you can find a way to make it work because it would be extremely handy to use.

    Thanks,

    Cameron

  • May 24, 2016 at 6:45 am

    #1881033

    Thanks for the script.

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

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