sql-server - 如何加密数据库的所有现有存储过程

Question

在通过 SQLCMD 脚本创建 SQL Server 2008 数据库的所有现有存储过程后，是否有可能对其进行加密？

我想这样做的原因如下:
我想开发不加密的存储过程，这样我就可以轻松地单击 SQL Server Management Studio 中的“修改”来检查其内容。
但是，对于部署，我想对它们进行加密，所以我想也许我可以编写一个脚本，仅在它们创建后对其进行加密。对于开发系统，我根本不会运行该脚本，而在最终用户系统上，该脚本将运行。

Answer 1

您可能想检查Encrypting all the Stored Procedures of a Database :

If you ever decide that you need to protect your SQL StoredProcedures, and thought encrypting was a good idea, BE VERY CAREFUL!!!Encrypting Database stored procedures SHOULD NOT be done withouthaving backup files or some sort of Source Control for the storedprocedures. The reason I say this is because, once they are encrypted,there is no turning around. (Yes, there are third party tools thatwill 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 concernedabout our code being compromised. So, to deliver the database, wedecided to encrypt all out stored procedures. Having over a hundredprocedures written, I didn't want to open each procedure and paste'WITH ENCRYPTION' in each and every stored procedure. (For those ofyou who do not know how to encrypt, refer How Do I Protect My StoredProcedure Code[^]). So I decided to make my own little C# applicationthat did the same.

This application is a console application madeusing Visual Studio 2005 and SQL server 2005. The input parameters aredatabase name, Server address, database username and password. Onceyou are able to provide these details, you are ready to have all yourstored procedures encrypted.

I have put the code of my applicationhere as is. For this code to work, you will need to add an"Microsft.SQlserver.SMO" refrence to the application, so that theclasses 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;
      }
   }
}