Encrypt all Stored Procedures 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.