Home > PowerShell > Import SQL Server Policies

Import SQL Server Policies

A PowerShell script to easily and quickly import policies into SLQ 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\_MarkitBuild\Policies\*.*' -Include *.xml foreach ($File in $PolicyFiles.FullName) { $PolicyXmlPath =$File #"C:\Delete_Data_R2\MultipleFiles\Markit-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:\PowershellScript\Apply_Policy.ps1 -Sqlserver ' +@servername +' -version '+@versionfolder+'"' exec master..xp_cmdshell @vsql

Categories: PowerShell
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: