Encrypting all Stored Procedures in a Database

Introduction

This code encrypts all Stored Procedures in a database.

Background

This version is a minor improvement of the original for .NET 4.0. It is important to add references to Microsoft.SQLServer.SMO, Microsoft.SQLServer.Management.sdk.sfc, and Microsoft.SQLServer.ConnectionInfo using the Browse method since these files do not appear when .NET 4.0 filtering is present.

To use this, create a new C# Console project in Visual Studio and overwrite the Program.cs file with the following code. Then add the references as described above and Run.

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();
        }
    }
}