Click here to Skip to main content
15,867,568 members
Articles / Security
Tip/Trick

Encrypt all Stored Procedures with PowerShell

Rate me:
Please Sign up or sign in to vote.
4.25/5 (3 votes)
7 Aug 2012CPOL 26.3K   5   8
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:

C++
$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)


Written By
Software Developer
Canada Canada
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionHow to do in Powershell? Pin
Cagdas 202314-Oct-23 11:30
Cagdas 202314-Oct-23 11:30 
QuestionReversing Pin
paul.d.mcswain17-Dec-19 6:33
paul.d.mcswain17-Dec-19 6:33 
PraiseVery useful Pin
Ramin Raad2-Jul-19 19:57
professionalRamin Raad2-Jul-19 19:57 
QuestionTriggers Pin
shpsyte21-Nov-16 23:55
shpsyte21-Nov-16 23:55 
GeneralMy vote of 5 Pin
Christian Amado6-Aug-12 10:13
professionalChristian Amado6-Aug-12 10:13 
SuggestionPowershell from Management Studio Pin
Lord Veovis4-Aug-12 23:58
Lord Veovis4-Aug-12 23:58 
SuggestionCode optimization Pin
Lord Veovis4-Aug-12 23:15
Lord Veovis4-Aug-12 23:15 
GeneralRe: Code optimization Pin
kennysun7-Aug-12 3:31
kennysun7-Aug-12 3:31 

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

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