Encrypt all existing Stored Procedures with PowerShell sql server

Question

I want to encrypt all stored procedures in existing database so i tried with shell scripts

$db = (new-Object Microsoft.SqlServer.Management.Smo.Server("SQL_instance")).Databases.Item("[DB_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 is working on one of my local server but not on client's server Source for this

i am getting following error Error

"I want to encrypt all stored procedures in existing database " -what could possibly go wrong! — Mitch Wheat, Jun 12 '14 at 11:33
@MitchWheat is there any different versions for shellscripts?? — Dgan, Jun 12 '14 at 11:49
Please [do not assume](http://stackoverflow.com/a/7671944/503046) the encryption is, well, secure. — vonPryz, Jun 12 '14 at 16:12
Try this, http://stackoverflow.com/a/8478071/930186 Its not PowerShell, but it works! — QV1, Mar 09 '15 at 09:11

score 0 · Answer 1 · answered Jun 12 '14 at 11:50

0

You script works fine for me - so I would suspect that you may need to run powershell in administrator mode or elevate its privileges..

answered Jun 12 '14 at 11:50

Jimbo

2,529
19
22

Encrypt all existing Stored Procedures with PowerShell sql server

1 Answers1