Encrypt All Stored procedures at once

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

  • how to Dcrypt it...

    Shivrudra W

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

    Very handy non the less.


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

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

  • 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.


    ALTER Procedure [dbo].[BCPReplicationIn]


    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 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