Creating a policy

In this recipe, we will create a policy programmatically using PowerShell.

Getting ready

In this recipe, we will use a condition called xp_cmdshell is disabled, which we created in a previous recipe. Feel free to substitute this with a condition that is available in your instance.

How to do it...

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

2. Import the SQLPS module, and create a new connection object as follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking
   $connectionstring = "server='KERRIGAN';Trusted_Connection=true"
   $conn = New-Object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection($connectionstring)
   $policystore = New-Object Microsoft.SqlServer.Management.DMF.PolicyStore($conn)

3. Add the following script and run:

   $policyName = "xp_cmdshell must be disabled"
   $conditionName = "xp_cmdshell is disabled"
   
   if ($policystore.Policies[$policyName])
   {
      $policystore.Policies[$policyName].Drop...