How to encrypt all existing stored procedures of a database

Question

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.

Answer 1

WITH ENCRYPTION means that the code behind the proc is not stored in the SysComments table.

You could write a script that does a exec sp_helptext 'MyProcName' and gets the contents into a VarChar (MAX) so it can hold multiline / large procedures easily and then modifiy the procedure from it's original state

CREATE MyProcName AS

SELECT SecretColumns From TopSecretTable

change CREATE to ALTER and AS surrounded by space or tab or newline (good place to use Regular Expressions) to WITH ENCRYPTION AS

ALTER MyProcName WITH ENCRYPTION AS

SELECT SecretColumns From TopSecretTable

This will hide all code for the stored proc on the production server.

You can put this in a LOOP or a CURSOR (not really a set based operation IMHO) for all objects of a specific type and/or naming convention that you want to encrypt, and run it every time you deploy.

Answer 2

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.

Answer 3

I would recommend creating the sproc in a multi-line string variable and then inserting or altering it using sp_executesql. The only annoying downside to this approach is doubling of single quotes for strings.

DECLARE @action varchar(max);
SET @action = 'CREATE'; /* or "ALTER" */

DECLARE @withEncryption varchar(max);
SET @withEncryption = ''; /* or "WITH ENCRYPTION" */

DECLARE @sql varchar(max);
SET @sql = @action + ' PROCEDURE dbo.Something'
    (
        ....
    ) ' + @withEncryption +
    ' AS
    BEGIN
        DECLARE @bob varchar(10);
        SET @bob = ''Bob'';
        ....
    END;
    ';

EXEC sp_executesql @statement = @sql;

[Note the whitespace around the variables.]

All of my scripts use this method, which works well once you get used to the quote doubling thing.

I also use a batch file to call the script, and SQLCMD-mode command line variables to select various behaviours, which makes it repeatable and easy to test.