###########################################################
# SQL 2008 Powershell Script for Policy Based Management
# Scenario: Create a  server policy to check if XPCmdShell is disabled and save it to file
#
# To run this Powershell script
# 1) Install SQL Server 2008
# 2) Run SQL PowerShell ( Start -> Run -> sqlps.exe)
# 3) Copy the following script and paste it in SQL powershell window  to run this script
###########################################################

######################################################
# Creates disconnected policy store
######################################################
function CreateOfflinePolicyStore
{
        $policyStore = new-object ('Microsoft.SqlServer.Management.Dmf.PolicyStore')
        ([Microsoft.SqlServer.Management.Sdk.Sfc.SfcConnectionContext]$policyStore).Mode = [Microsoft.SqlServer.Management.Sdk.Sfc.SfcConnectionContextMode]::Offline
 
        $policyStore;
}

######################################################
# Creates  Expression Node for (XpCommandShellEnabled=False)
######################################################
function GetExpressionNode
{
        $nodeOperator = [Microsoft.SqlServer.Management.Dmf.OperatorType]::EQ
        $nodeAttribute = new-object ('Microsoft.SqlServer.Management.Dmf.ExpressionNodeAttribute')("XPCmdShellEnabled")
        $nodeFunction = [Microsoft.SqlServer.Management.Dmf.ExpressionNode]::ConstructNode($False)
        $expressionNode = new-object ('Microsoft.SqlServer.Management.Dmf.ExpressionNodeOperator')($nodeOperator, $nodeAttribute, $nodeFunction)

        $expressionNode;
}

######################################################
# Creates condition based on ISurfaceAreaFacet Facet
######################################################
function CreateCondition
{
        Param($policyStore)
        
        $conditionName = "Surface_Area_Config_XPCmdShell_Condition"
        $condition = new-object ('Microsoft.SqlServer.Management.Dmf.Condition')($policyStore, $conditionName)
        $condition.Facet =  "ISurfaceAreaFacet"
        $condition.ExpressionNode = GetExpressionNode
        $policyStore.Conditions.Add($condition)
        
        $condition;
}

######################################################
#Gets corresponding facet name for the facet that is referred in the condition
######################################################
function GetFacetName
{
        Param($condition)
        
        $facetInfo = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets[$condition.Facet];
        
        $facetInfo.FacetType.Name;
}

######################################################
# Creates objectset based on the facet that is referred by the condition
######################################################
function CreateObjectSet
{
        Param($policyStore, $policyName, $condition)
        
        $objectSetName = $policyName + "_ObjectSet"
        $objectSet = new-object ('Microsoft.SqlServer.Management.Dmf.ObjectSet')($policyStore, $objectSetName)
        $objectSet.Facet = GetFacetName $condition
        $policyStore.ObjectSets.Add($objectSet)
        
        $objectSet;
}

######################################################
# Creates policy object and sets the condition and object set references
######################################################
function CreatePolicy
{
        Param($policyStore,  $condition, $objectSet)
        
        $policy = new-object ('Microsoft.SqlServer.Management.Dmf.Policy')($policyStore, $policyName)
        $policy.Condition = $condition.Name
        $policy.ObjectSet = $objectSet.Name
        $policyStore.Policies.Add($policy)

        $policy;
}

######################################################
# Saves given policy object to a file.
# To view this policy file, you can launch SQL Server 2008 Management Studio,
#       and click on menu File -> Open -> Policy, Browse to %TEMP% to locate this xml file
######################################################
function ExportPolicyToFile
{
        Param($policy,  $policyFilePath)
        
        $xmlWriter = [System.Xml.XmlWriter]::Create($policyFilePath)
        $policy.Serialize($xmlWriter)
        $xmlWriter.Close()
        write-host "Policy Saved to file:" + $policyFilePath
}



################
## Main code
################

$policyName =  "Surface_Area_Config_XPCmdShell_Policy"

# Create Offline Policy store
$policyStore = CreateOfflinePolicyStore

# Create Condition
$condition = CreateCondition $policyStore

# Create ObjectSet
$objectSet = CreateObjectSet $policyStore $policyName $condition

# Create Policy
$policy = CreatePolicy $policyStore $condition $objectSet

# Export Policy Store to a temp file
# This file can be viewed using SQL Server 2008 Management Studio. (Launch ssms, click on menu File -> Open -> Policy)
$policyFilePath  = $Env:Temp + "\" + $policy.Name + ".xml"
ExportPolicyToFile $policy $policyFilePath

#This posting is provided "AS IS" with no warranties, and confers no rights.
#Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm