Use PowerShell to encrypt SQL Server Stored Procedures

The following code uses a PowerShell script to encrypt all stored procedures in SQL Server.

For security reasons, we use with encryption to encrypt the stored procedure. But what should we do if we want to update the encrypted stored procedure? I have seen some people write a C # console program. I am prompted to use PowerShell to call 'Microsoft. SqlServer. Management. Smo.

First, open PowerShell. You need to determine whether your SQL Server version supports PowerShell. Here I use SQL Server 2008.

Open 'Microsoft SQL Server Management Studio '-> open 'object Explorer'-> <name of the database you want to process>, right-click it, and open 'start powershell'. A PowerShell Prompt window is displayed.

The modification code is as follows:

$ Srv = new-Object Microsoft. SqlServer. Management. Smo. Server ("[DataBase Engine Name]")
$ Db = New-Object Microsoft. SqlServer. Management. Smo. Database
$ Db = $ srv. 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 ."
}
}
}
}

It depends on the number of stored procedures.

Original English, OSCHINA Original translation