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
– Mitch Wheat, Commented Jun 12, 2014 at 11:33
@MitchWheat is there any different versions for shellscripts?? — Dgan
– Dgan, Commented Jun 12, 2014 at 11:49
Try this, stackoverflow.com/a/8478071/930186 Its not PowerShell, but it works! — QV1
– QV1, Commented Mar 9, 2015 at 9:11

Jimbo · Accepted Answer · 2014-06-12 11:50:47Z

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, 2014 at 11:50

Jimbo

2,53719 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Encrypt all existing Stored Procedures with PowerShell sql server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related