Is there any possibility to encrypt all existing stored procedures of a SQL Server 2008 database AFTER they have been created via an SQLCMD script?
The reason I want to do this is the following:
I'd like to develop the stored procedures without encryption so I can easily click on "Modify" in SQL Server Management Studio to check their contents.
However, for the deployment I'd like to encrypt them so I thought that maybe I could write a script which encrypts them only after they're created. For dev systems I simply wouldn't run the script while on end-user systems the script would be run.
I have the same problem.
My solution is to put "-- WITH ENCRYPTION" in all of my stored procedures. This version is used by developers and stored in source control.
I then use a tool (like sed) in my build to replace "-- WITH ENCRYPTION" with "WITH ENCRYPTION" on the files before I send them to be installed.
For a pure SQL solution you could use REPLACE.