Click here to Skip to main content
Click here to Skip to main content
Alternative Tip

Encrypting all Stored Procedures in a Database

, 11 Apr 2012
Rate this:
Please Sign up or sign in to vote.
This is an alternative for "Encrypting all Stored Procedures of 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();
        }
    }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

jroughgarden
Technical Lead
United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140821.2 | Last Updated 11 Apr 2012
Article Copyright 2012 by jroughgarden
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid