数据库中所有存储过程的加密

引言

这段代码可以加密数据库中的所有存储过程。

背景

这是原始代码在 .NET 4.0 上的一个小的改进版本。 重要的是使用“浏览”方法添加对 Microsoft.SQLServer.SMO、Microsoft.SQLServer.Management.sdk.sfc 和 Microsoft.SQLServer.ConnectionInfo 的引用，因为在 .NET 4.0 过滤存在时，这些文件不会显示出来。

要使用此代码，请在 Visual Studio 中创建一个新的 C# 控制台项目，并用以下代码覆盖 Program.cs 文件。 然后按照上述描述添加引用并运行。

Using the Code

using System;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace EncryptSprocs
{
    class Program
    {
        static void Main(string[] args)
        {
            //Connect to the local, default instance of SQL Server. 
            var serverConnection = new ServerConnection {LoginSecure = false};

            Console.WriteLine("Enter name or IP address of the database server.");
            serverConnection.ServerInstance = Console.ReadLine();

            Console.WriteLine("Enter name of the database");
            string dbRequested = Console.ReadLine();

            Console.WriteLine("Enter user id");
            serverConnection.Login = Console.ReadLine();

            Console.WriteLine("Enter password");
            serverConnection.Password = Console.ReadLine();
            Console.WriteLine(" ");

            var srv = new Server();
            try // Check to see if server connection details are ok.
            {
                srv = new Server(serverConnection);
            }
            catch
            {
                Console.WriteLine("Server details are incorrect;"
                   + " please restart the application.");
                Console.ReadLine();
                Environment.Exit(Environment.ExitCode);
            }

            var db = new Database();
            try // Check to see if database exists.
            {
                db = srv.Databases[dbRequested];
                if (db == null)
                    throw new Exception();
            }
            catch
            {
                Console.WriteLine("Database does not exist on the current server;"
                   + " please restart the application.");
                Console.ReadLine();
                Environment.Exit(Environment.ExitCode);
            }

            Console.WriteLine("Encrypted stored procedures: ");
            var sp = new StoredProcedure();
            for (int i = 0; i < db.StoredProcedures.Count; i++)
            {
                sp = db.StoredProcedures[i];
                if (!sp.IsSystemObject)         // Exclude System stored procedures
                {
                    if (!sp.IsEncrypted)        // Exclude already encrypted stored procedures
                    {
                        sp.TextMode = false;
                        sp.IsEncrypted = true;
                        sp.TextMode = true;
                        sp.Alter();
                        Console.WriteLine("   " + sp.Name); // display name of the SP.                        
                    }
                }
            }
            Console.WriteLine(""); 
            Console.WriteLine("Press any key to exit...");
            Console.ReadLine();
        }
    }
}