Sethu's blog

dev@sqlservr.microsoft

SQL 2008 Powershell Script for creating a Policy and saving to file

SQL 2008 Powershell Script for creating a Policy and saving to file

Rate This
  • Comments 1

###########################################################
# 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

Comments
Page 1 of 1 (1 items)
Leave a Comment
  • Please add 8 and 1 and type the answer here:
  • Post