SQL Server 2014 with PowerShell v5 Cookbook

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 PowerShell ISE as administrator.
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 = "localhost" $server ...