Click here to Skip to main content
Click here to Skip to main content

Encrypt all Stored Procedures with PowerShell

, 7 Aug 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
encrypt all stored procedures in SQL server with powershell

Introduction

The code underneath shows how to use PowerShell script to encrypt all Stored Procedures in SQL Server once.

Background

For security reason, we put 'WITH ENCRYPTION' to encrypt the stored procedure when we create it. But if we want to update all the stored procedures to encryption, how to do it in one hit? Someone post the C# console application already. Thanks their hint. The point is we need 'Microsoft.SqlServer.Management.Smo' to get it done. Here is my solution to use powershell as same way.

Using the code

First, we need to open PowerShell. You need confirm that your SQL Server version support PowerShell. We are using SQL 2008.

Open 'Microsoft SQL Server Management Studio' -> open 'Object Explorer'-> had better go to the database which you want to update, right click and open 'Start PowerShell', then new PowerShell prompt window will popup.

Modify the code below, change database engine name and database name to yours, copy and paste into the PowerShell window, hit return to run it:

$db = (new-Object Microsoft.SqlServer.Management.Smo.Server("[DataBase Engine Name]")).Databases.Item("[DataBase Name]") 

Foreach ($sp in $db.StoredProcedures){
  if(!$sp.IsSystemObject){
    if (!$sp.IsEncrypted){
        $sp.TextMode = $false;
        $sp.IsEncrypted = $true;
        $sp.TextMode = $true;
        try{
            $sp.Alter();
        }catch{
            Write-Host "$sp.Name fail to encrypted."
        }
     }
  }
}

Be patient, it will be finished for a while depend on the number of the Stored Procedures in your database.

License

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

Share

About the Author

kennysun
Software Developer
Canada Canada
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 PinmemberChristian Amado6-Aug-12 11:13 
SuggestionPowershell from Management Studio PinmemberLord Veovis5-Aug-12 0:58 
SuggestionCode optimization PinmemberLord Veovis5-Aug-12 0:15 
GeneralRe: Code optimization Pinmemberkennysun7-Aug-12 4:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 7 Aug 2012
Article Copyright 2012 by kennysun
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid