In this recipe, we will create a policy programmatically using PowerShell.
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.
These are the steps required to create a policy:
Open PowerShell ISE as an administrator.
Import the
SQLPSmodule 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)
Add the following script and run it:
$policyName = "xp_cmdshell must be disabled" $conditionName = "xp_cmdshell is disabled" if ($policyStore.Policies[$policyName]) ...