Importing a policy

This recipe will show how you can import a policy stored as an XML file into SQL Server.

Getting ready

In this recipe, we will use an XML policy that comes with the default SQL Server installation. This policy is called Guest Permissions.xml, and is stored in C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Policies\DatabaseEngine\1033

Feel free to substitute this with a policy you have available in your system.

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Import the SQLPS module, and create a new SMO Server object:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking

3. Add the following script and run:

   $connectionstring = "server='KERRIGAN';Trusted_Connection=true"
   $conn = New-Object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection($connectionstring)
   
   #connect to policystore
   $policyStore = New-Object Microsoft.SqlServer.Management.DMF.PolicyStore($conn)
   
   #you can replace...