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

You might want to check Encrypting all the Stored Procedures of a Database :

If you ever decide that you need to protect your SQL Stored Procedures, and thought encrypting was a good idea, BE VERY CAREFUL!!! Encrypting Database stored procedures SHOULD NOT be done without having backup files or some sort of Source Control for the stored procedures. The reason I say this is because, once they are encrypted, there is no turning around. (Yes, there are third party tools that will decrypt your code, but Why go through that trouble.)

This trick is something I developed because my company needed to host the application on a different server, and we were concerned about our code being compromised. So, to deliver the database, we decided to encrypt all out stored procedures. Having over a hundred procedures written, I didn't want to open each procedure and paste 'WITH ENCRYPTION' in each and every stored procedure. (For those of you who do not know how to encrypt, refer How Do I Protect My Stored Procedure Code[^]). So I decided to make my own little C# application that did the same.

This application is a console application made using Visual Studio 2005 and SQL server 2005. The input parameters are database name, Server address, database username and password. Once you are able to provide these details, you are ready to have all your stored procedures encrypted.

I have put the code of my application here as is. For this code to work, you will need to add an "Microsft.SQlserver.SMO" refrence to the application, so that the classes such as "Database" and "StoredProcedure" are accessible.

BEFORE YOU DO THIS, TAKE A BACKUP!!!!!!!

//Connect to the local, default instance of SQL Server. 
string DB = "";
ServerConnection objServerCOnnection = new ServerConnection();
objServerCOnnection.LoginSecure = false;
Console.WriteLine("Enter name or IP Address of the Database Server.");
objServerCOnnection.ServerInstance = Console.ReadLine();
Console.WriteLine("Enter name of the Database");
DB = Console.ReadLine();
Console.WriteLine("Enter user id");
objServerCOnnection.Login = Console.ReadLine();
Console.WriteLine("Enter Password");
objServerCOnnection.Password = Console.ReadLine();
Console.WriteLine(" ");
Server srv = new Server();
try // Check to see if server connection details are ok.
{
   srv = new Server(objServerCOnnection);
   if (srv == null)
   {
      Console.WriteLine("Server details entered are wrong,"
         + " Please restart the application");
      Console.ReadLine();
      System.Environment.Exit(System.Environment.ExitCode);
   }
}
catch
{
   Console.WriteLine("Server details entered are wrong,"
      + " Please restart the application");
   Console.ReadLine();
   System.Environment.Exit(System.Environment.ExitCode);
}
Database db = new Database();
try // Check to see if database exists.
{
   db = srv.Databases[DB];
   if (db == null)
   {
      Console.WriteLine("Database does not exist on the current server,"
         + " Please restart the application");
      Console.ReadLine();
      System.Environment.Exit(System.Environment.ExitCode);
   }
}
catch
{
   Console.WriteLine("Database does not exist on the current server,"
      + " Please restart the application");
   Console.ReadLine();
   System.Environment.Exit(System.Environment.ExitCode);
}
string allSP = "";
                
for (int i = 0; i < db.StoredProcedures.Count; i++)
{
   //Define a StoredProcedure object variable by supplying the parent database 
   //and name arguments in the constructor. 
   StoredProcedure sp;
   sp = new StoredProcedure();
   sp = db.StoredProcedures[i];
   if (!sp.IsSystemObject)// Exclude System stored procedures
   {
      if (!sp.IsEncrypted) // Exclude already encrypted stored procedures
      {
         string text = "";// = sp.TextBody;
         sp.TextMode = false;
         sp.IsEncrypted = true;
         sp.TextMode = true;
         sp.Alter();
 
         Console.WriteLine(sp.Name); // display name of the encrypted SP.
         sp = null;
         text = null;
      }
   }
}

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

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.