I created the following policy. It's a basic policy for enforcing database settings. I then created a database in violation of the policy and didn't generate an error. I also altered the database to violate the policy and still didn't get any policy errors.
alter database test2
set auto_close on
<?xml version="1.0" encoding="utf-8" ?>
- <SfcSerializerFragment SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.PolicyStore">
- <SfcInstance SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.Policy">
<SfcProperty Name="Name">Enterprise_DatabaseCreate</SfcProperty>
<SfcProperty Name="Description" />
<SfcProperty Name="Condition">DatabaseDefaults</SfcProperty>
<SfcProperty Name="PolicyGroup">Enterprise</SfcProperty>
<SfcProperty Name="Enabled">True</SfcProperty>
<SfcProperty Name="AutomatedPolicyExecutionMode">CheckOnChanges</SfcProperty>
- <SfcInstance SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.TargetFilter">
<SfcProperty Name="TargetType">DATABASE</SfcProperty>
<SfcProperty Name="Filter">Server/Database</SfcProperty>
<SfcProperty Name="TargetTypeSkeleton">Server/Database</SfcProperty>
</SfcInstance>
</SfcInstance>
- <SfcInstance SfcDomain="Dmf" SfcType="Microsoft.SqlServer.Management.Dmf.Condition">
<SfcProperty Name="Name">DatabaseDefaults</SfcProperty>
<SfcProperty Name="Description" />
<SfcProperty Name="Expression"><Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>AND</OpType> <Count>2</Count> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoClose</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>False</Value> </Constant> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoCreateStatistics</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>True</Value> </Constant> </Operator> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoCreateStatisticsEnabled</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>True</Value> </Constant> </Operator> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoShrink</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>False</Value> </Constant> </Operator> </Operator> <Operator> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <Name>AutoUpdateStatistics</Name> </Attribute> <Constant> <ObjType>System.Boolean</ObjType> <Value>True</Value> </Constant> </Operator> </Operator></SfcProperty>
<SfcProperty Name="Facet">IDatabaseFacet</SfcProperty>
</SfcInstance>
</SfcSerializerFragment>
I then created a subsequent policy (policy 2) with just the "AUTO CLOSE = FALSE" and this will ONLY throw an error when I "TEST" the policy. It doesn't throw the error when I run the ALTER DATABASE TSQL.
I created a 3rd database in violation of POLICY2 and was successfuly able to create the db without generating an error.
Is policy manager working properly? Shouldn't it work on CREATE DATABASE? Shouldn't it work without selecting "TEST" on policy?
Thanks
Steve
(SteveBouffard@.discussions.microsoft.com) writes: > I created the following policy. It's a basic policy for enforcing > database settings. I then created a database in violation of the policy > and didn't generate an error. I also altered the database to violate > the policy and still didn't get any policy errors. But if you look in Mgmt Studio, doesn't the database have some warning sign attached to it? I was not able to import your XML document, but you have CheckOnChanges. To get an error when you violated a policy you need Enforce. But when I tried to set up a policy to prohibit databases with auto-close, Enforce was greyed out. I guess this is because CREATE and ALTER DATABASES are commands that cannot be rolled back, so it is not possible to enforce them. -- Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||The execution modes for a policy are determined by the facet. The facet defines the events to listen to for Enforce and Check on Change (CoC). In order for the facet to define events for Enforce, the target type (e.g. database, table, view, etc) must be completely transactional. In the case of Database, create database is not completely transactional and therefore enforce is not support on conditions built-on database facets. Yes, the create database transaction can be canceled, but the system is not returned to the previous state. Therefore, DMF doesn't support enforce.
Cheers,
Dan
|||ok, thanks