Creating a stored procedure

This recipe shows how to create an encrypted stored procedure using SMO and PowerShell.

Getting ready

The T-SQL equivalent of the encrypted stored procedure we are about to recreate in PowerShell is as follows:

CREATE PROCEDURE [dbo].[uspGetPersonByLastName] @LastName [varchar](50)
WITH ENCRYPTION
AS
SELECT 
   TOP 10 
   BusinessEntityID,
   LastName
FROM 
   Person.Person
WHERE 
   LastName = @LastName

How to do it...

Follow these steps to create the uspGetPersonByLastName stored procedure using PowerShell:

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Import the SQLPS module, and create a new SMO Server object:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking
   
   #replace this with your instance name
   $instanceName = "KERRIGAN"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

3. Add the following script and run it:

   $dbName = "AdventureWorks2008R2...