Click here to Skip to main content
13,096,858 members (82,843 online)
Click here to Skip to main content
Add your own
alternative version


9 bookmarked
Posted 11 Apr 2012

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".


This code encrypts all Stored Procedures in a database.


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);
                Console.WriteLine("Server details are incorrect;"
                   + " please restart the application.");

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

            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;
                        Console.WriteLine("   " + sp.Name); // display name of the SP.                        
            Console.WriteLine("Press any key to exit...");


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


About the Author

Technical Lead
United States United States
This member doesn't quite have enough reputation to be able to display their biography and homepage.

You may also be interested in...

Comments and Discussions

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