Import SQL Server Policies

A PowerShell script to easily and quickly import policies into SQL Server.

param ([string] $Sqlserver) 
Import-Module sqlps -DisableNameChecking 
$Connectionstring = "server='"+$Sqlserver+"';Trusted_Connection=true" 
$Conn = New-Object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection($Connectionstring) $PolicyStore = New-Object Microsoft.SqlServer.Management.DMF.PolicyStore($Conn)
$PolicyFiles=Get-ChildItem -Path 'C:\Program Files\Microsoft SQL Server\110\_MarketBuildPolicies\*.*' -Include *.xml 
foreach ($File in $PolicyFiles.FullName) 
 { 
   $PolicyXmlPath =$File #"C:\Delete_Data_R2\MultipleFilesMarket-Check-SQLServer2008R2-Build.XML"     
   $xml = [System.Xml.XmlReader]::Create($PolicyXmlPath) 
   $PolicyStore.ImportPolicy($xml,`    
   [Microsoft.SqlServer.Management.Dmf.ImportPolicyEnabledState]::Unchanged, $true, $true)` 
   |Select-Object Name,Enabled 
 }

image

image

image

Invoking PowerShell script from T-SQL

declare @servername    varchar(500) 
declare @vsql          nvarchar(4000) 
declare @versionfolder varchar(4) 
set @servername=@@servername 
select @versionfolder=max(compatibility_level) from sys.databases 
set @vsql='"powershell.exe C:PowershellScriptApply_Policy.ps1 -Sqlserver ' +@servername +' -version '+@versionfolder+'"' 
exec master..xp_cmdshell @vsql

Leave a Reply

Your email address will not be published. Required fields are marked *