Creating a policy

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

Getting ready

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

How to do it...

These are the steps required to create a policy:

1. Open PowerShell ISE as an administrator.
2. Import the SQLPS module and create a new SQL Server object as follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking
   
   $connectionString = "server='localhost';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 it:

   $policyName = "xp_cmdshell must be disabled"
   $conditionName = "xp_cmdshell is disabled"
   ...