Encrypting stored procedures

thobbs

When working at a client site we usually deploy a complete set of stored procs (the main functionality of our systems) and test, tweak until all is well. As a final act I'd like to be able to run a tool which rescripts all our procedures with encryption.

Is there a Redgate product that does this?

Michelle T

There is an option in SQL Compare, 'Add With Encryption', which adds encryption to all of the objects in a synchronization.

I think you can probably do what you're after via the SQL Compare product with the following process:

1) Compare an empty database with the database containing the set of stored procedures
2) Check the included objects in this synchronization (the tab with Include Dependancies in the synchronization wizard) and note them down.
3) Go to the next step in the wizard and save out the script.
4) Reverse the synchronization direction (so that the database with the correct information is to be synchronized to the empty database), put the Add With Encryption option on, and select the objects which were included in the first synchronization.
5) Save out the script for this synchronization.
6) Back up your correct database, as you're about to make modifications
7) Run the script full of DROP statements that you get from step 3)
8 ) Run the script full of CREATE statements that you got from step 5)

With the SQL Toolkit you could write a .NET program to automate this process.

I recommend getting a trial copy of SQL Compare and trying out the process on a typical set of stored procedures, and seeing if it works for you. I'm sure that our Support department (support@red-gate.com) will be happy to answer further questions if it doesn't quite work out, or you can contact me at michelle.taylor@red-gate.com.