Click here to Skip to main content
12,759,698 members (32,210 online)
Click here to Skip to main content
Add your own
alternative version


5 bookmarked
Posted 3 Aug 2012

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


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


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.IsEncrypted){
        $sp.TextMode = $false;
        $sp.IsEncrypted = $true;
        $sp.TextMode = $true;
            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.


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


About the Author

Software Developer
Canada Canada
No Biography provided

You may also be interested in...


Comments and Discussions

QuestionTriggers Pin
shpsyte22-Nov-16 0:55
membershpsyte22-Nov-16 0:55 
GeneralMy vote of 5 Pin
Christian Amado6-Aug-12 11:13
memberChristian Amado6-Aug-12 11:13 
SuggestionPowershell from Management Studio Pin
Lord Veovis5-Aug-12 0:58
memberLord Veovis5-Aug-12 0:58 
SuggestionCode optimization Pin
Lord Veovis5-Aug-12 0:15
memberLord Veovis5-Aug-12 0:15 
GeneralRe: Code optimization Pin
kennysun7-Aug-12 4:31
memberkennysun7-Aug-12 4: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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170217.1 | Last Updated 7 Aug 2012
Article Copyright 2012 by kennysun
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid