Policy-based management is a new feature in SQL Server 2008 that lets you set the criteria for the “behavior” of various SQL Server objects. It also provides a mechanism to enforce policy. Policies can be created and enabled on the server, giving the DBA more control. This is very important in the context of creating common security practices in a company. I’d like to describe the steps that I took to create a flexible, policy-based mechanism for the validation of various security requirements and for enforcing the policies if a violation was found.

Talking Policy Management

Policy-based management in SQL Server 2008 is implemented as a set of rules set by the DBA for validating whether target objects (e.g., servers, databases, tables) comply with a specific policy. Verifiable properties of the targets are exposed through predefined objects—facets—which users can’t modify.

The state of the facet’s property is verified through a Boolean expression and is called a condition, which can be constructed by the user. A condition specifies the allowed state of a facet. Multiple properties of the same facet can be evaluated in one condition using the Boolean operators AND, OR. Each policy can have only one condition that checks the behavior of the particular targets.

All policies can be executed in On demand mode and On schedule mode. Some policies support the On change: log only mode. Very few policies can be regulated by the On change: prevent mode.

I assume that the reader is familiar with the basic design of policy-based management in SQL Server 2008 and its main components: policies, conditions, and facets. For specific information, see the related SQL Server Books Online (BOL) article"Administering Servers by Using Policy-Based Management."

The Challenge

One of my corporate clients in the financial industry (“the Company”) asked me to help develop a policy-based management system that would govern all security requirements for new and existing installations of SQL Server 2008. At the Company, the Windows engineering department is responsible for providing scripts for common, unattended SQL Server 2008 installations in each business division. This department wants to unify security criteria for all 2008 servers across the company, independent of environment, application, and support model.

They gave me a list of generic security requirements that I was supposed to convert into policies. Most of the requirements were based on Microsoft best practices; some were company-specific. All policies needed to be flexible enough to allow the DBA to enter exceptions if needed, without policy modification. SQL Server 2008 comes with a set of built-in policies.

These policies aren’t installed by default, but they can be easily imported to the server. For details, see the BOL article "How to: Export and Import a Policy-Based Management Policy."

Unfortunately, the current, out-of-the-box implementation of policy-based management in SQL Server 2008 has a few limitations:

  • Policies aren’t flexible enough. It’s difficult to create a generic policy common to each individual server that a DBA supports.
  • Only a few policies allow On change: prevent mode, and the DBA doesn’t have a policy enforcement mechanism.
  • Only the simplest rules are implemented in built-in policies.

Solution Description

I addressed the limitations of the out-of-the-box implementation by creating a table-driven solution that lets a DBA insert policy exceptions and regulate policy execution. As part of my solution, I built a mechanism of policy enforcement through a scheduled job that evaluates the policy and, if needed and requested, enforces it immediately.

After consultation with the client, I created a schedule called Verify_Policies_Schedule. All created policies were associated with the On schedule evaluation mode and this particular schedule. When at least one policy is scheduled to execute, SQL Server generates a job. I modified this system-created job by adding flexibility and an additional step that enforces policy if a violation is discovered.

I created in msdb four new SQL Server tables to store policy configuration, desired execution mode, and policy evaluation results.

dbo.PolicyConfiguration table. Exceptions to regular policy conditions can be entered in the dbo.PolicyConfiguration table. (See Listing 1, which creates this table.)

Listing 1: Create_PolicyConfiguration_Table
USE msdb  
SET NOCOUNT ON  
GO  
IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyConfiguration')  
  DROP TABLE \[dbo\].\[PolicyConfiguration\]  
GO  
CREATE TABLE \[dbo\].\[PolicyConfiguration\](  
\[PolicyConfigurationID\] \[bigint\] NOT NULL IDENTITY(1,1),        
  \[EvalPolicy\] \[varchar\](500) NOT NULL,        
  \[Target\] \[varchar\](400) NOT NULL,        
  \[IncludeFlag\] \[int\] NULL,     --Include = 1, Exclude = 2        
  CONSTRAINT PK_PolicyConfiguration PRIMARY KEY (PolicyConfigurationID),        
  CONSTRAINT UQ_PolicyConfiguration UNIQUE (EvalPolicy, Target)  
) ON \[PRIMARY\]  
IF @@ERROR = 0  
  PRINT 'TABLE PolicyConfiguration IN msdb CREATED SUCCESSFULLY'  
ELSE  
PRINT 'COULD NOT CREATE TABLE PolicyConfiguration IN msdb'  
GO

To add an exception to the policy for a particular server, database, or object, the DBA just enters the records into this table. Columns in this table store the following information:
• PolicyConfigurationID—primary key
• EvalPolicy—policy name
• Target—name of the object (database, server) that should be included or excluded from the policy
• IncludeFlag—1 (object included); 2 (object excluded)

For example, if you want to make an exception to the policy “Blank Password For SQL Logins” on ServerA, insert the following record into dbo.PolicyConfiguration:

INSERT dbo.PolicyConfiguration (EvalPolicy,    
Target, IncludeFlag)  
VALUES ('Blank Password For SQL Logins',    
'ServerA', 2)

Each policy can be evaluated in one of two modes: Mode Value 0 stands for Display Only mode—it only evaluates the policy, and no policy enforcement occurs if a violation is found. Mode Value 1 stands for Enforce Policy mode and enforces the policy if a violation is found.

dbo.PolicyExecution table. The evaluation mode for policy execution can be set individually in the dbo.PolicyExecution table, which Listing 2 creates. In this table, columns store the following information:

  • PolicyExecutionID—primary key
  • EvalPolicy—policy name
  • EvaluationMode—0 (display only); 1 (enforce policy)
Listing 2: Create_PolicyExecution_Table
USE msdb  
SET NOCOUNT ON  
GO    

 IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyExecution')    
  DROP TABLE \[dbo\].\[PolicyExecution\]  
GO  
CREATE TABLE \[dbo\].\[PolicyExecution\](        
  \[PolicyExecutionID\] \[bigint\] NOT NULL IDENTITY(1,1),      
  \[EvalPolicy\] \[varchar\](500) NOT NULL,        
  \[EvaluationMode\] \[int\] NOT NULL DEFAULT(0),      
  CONSTRAINT PK_PolicyExecution PRIMARY KEY (PolicyExecutionID),      
  CONSTRAINT UQ_PolicyExecution UNIQUE (EvalPolicy),      
  CHECK (EvaluationMode >= 0 and EvaluationMode <= 1)  
) ON \[PRIMARY\]  
IF @@ERROR = 0         
  PRINT 'TABLE PolicyExecution IN msdb CREATED SUCCESSFULLY'  
ELSE   
PRINT   'COULD NOT CREATE TABLE PolicyExecution IN msdb'  
GO  

For example, to see whether the policy “Blank Password For SQL Logins” was violated without enforcing password assignment, insert the following record into dbo.PolicyExecution:

INSERT dbo.PolicyExecution (EvalPolicy,    
EvaluationMode) VALUES ('Blank Password    
For SQL Logins', 0)

To immediately enforce the policy by assigning some default password, insert the following row into dbo.PolicyExecution:

INSERT dbo.PolicyExecution (EvalPolicy,    
EvaluationMode)  
VALUES ('Blank Password    
For SQL Logins', 1)

dbo.PolicyEvaluation table and dbo.PolicyEvaluation_FailureDetails table. When policies are executed on SQL Server, the results are accumulated in two system tables located in the msdb database: dbo.syspolicy_policy_execution_history and dbo.syspolicy_policy_execution_history_details. The job I created extracts the results of the most recent scheduled policy evaluations from dbo.syspolicy_policy_execution_history and stores them in a new table called msdb.dbo.PolicyEvaluation.

Violations of the most recent policy evaluations are extracted from the dbo.syspolicy_policy_execution_history_details system table and stored in the table called msdb.dbo.PolicyEvaluation_FailureDetails. Web Listing 1 creates these two tables.

Web Listing 1: Create_PolicyEvaluation_Tables
USE msdb
GO
IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyEvaluation')
     DROP TABLE \[dbo\].\[PolicyEvaluation\]
GO
CREATE TABLE \[dbo\].\[PolicyEvaluation\](
  \[rec_id\] \[int\] NOT NULL,
  \[history_id\] \[bigint\] NOT NULL,
  \[policy_id\] \[int\] NOT NULL,
  \[EvalPolicy\] \[varchar\](500) NOT NULL,
  \[EvalDateTime\] \[datetime\] NULL,
  \[SuccessFlag\] \[int\] NULL,
  \[FixFlag\] \[int\] NULL,
  \[ErrorMsg\] \[nvarchar\](max) NULL,
  CONSTRAINT PK_PolicyEvaluation PRIMARY KEY (rec_id)
) ON \[PRIMARY\]
IF @@ERROR = 0
    PRINT 'TABLE PolicyEvaluation IN msdb CREATED SUCCESSFULLY'
ELSE
    PRINT 'COULD NOT CREATE TABLE PolicyEvaluation IN msdb'
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'PolicyEvaluation_FailureDetails')
    DROP TABLE \[dbo\].\[PolicyEvaluation_FailureDetails\]
GO
CREATE TABLE \[dbo\].\[PolicyEvaluation_FailureDetails\](
  \[failure_id\] \[int\] NOT NULL,
  \[EvalPolicy\] \[varchar\](500) NOT NULL,
  \[Target\] \[varchar\](400) NOT NULL,
  \[EvalDateTime\] \[datetime\] NULL,
  \[EvalResults\] \[xml\] NULL,
  \[FixFlag\] \[int\] NULL,
  \[FixErrorMsg\] \[nvarchar\](max) NULL,
  CONSTRAINT PK_PolicyEvaluation_FailureDetails PRIMARY KEY (failure_id)
) ON \[PRIMARY\]
IF @@ERROR = 0
    PRINT 'TABLE PolicyEvaluation_FailureDetails IN msdb CREATED SUCCESSFULLY'
ELSE
    PRINT 'COULD NOT CREATE TABLE PolicyEvaluation_FailureDetails IN msdb'
GO

Table 1 shows the column names and descriptions for table dbo.PolicyEvaluation and the dbo.PolicyEvaluation_FailureDetails.

For brevity’s sake, in the Table column, a 1 corresponds to the dbo.PolicyEvaluation table and a 2 corresponds to the dbo.PolicyEvaluation_FailureDetails table.

Creating a Policy

To illustrate the technique I used to create policy, let’s look at how I built the policy “Database DDL Triggers Enabled.” The Company has a trigger-based process that collects information about each user login to each database, except tempdb.

I was asked to create a policy for checking whether all mandatory DDL triggers were enabled in each database on each SQL Server 2005 or later instance. To do so, follow these steps:

  1. Decide on a policy name. We need to know the policy name to enter policy exceptions (if any) in the msdb.dbo.PolicyConfiguration table.
  2. Decide on server restrictions. As DDL triggers were introduced in the SQL Server 2005 release, we need to include this filter. Figure 1 shows the condition that verifies the above-mentioned criteria.
  3. Decide on database restrictions. I created the condition “No tempdb” based on the Database facet that includes all user databases and three remaining system databases. This condition, which you can see in Figure 2, also makes sure that the database status is normal.
  4. Create a condition to validate presence of disabled triggers. Any of the facets that allow checking conditions against database objects could be chosen here, such as Database or Database Security. In Listing 3, you can see an expression that shows how many user-created database DDL triggers are enabled in the database.
    Listing 3: Expression that Shows How Many User-Created Database DDL Triggers are Enabled
    SELECT COUNT(*)  
    FROM  
      sys.triggers  
    WHERE  
    is_disabled = 1
    AND is_ms_shipped = 0  
    and parent_class_desc = 'DATABASE'  
    and name IN (SELECT Target FROM    
      msdb.dbo.PolicyConfiguration WHERE    
    EvalPolicy = 'Database DDL Triggers
      Enabled' AND IncludeFlag = 1)

    Then we use the ExecuteSQL function, which allows embedding of a SELECT statement in a Policy-Based Management expression, which Listing 4 shows.

    Listing 4: Policy-Based Management Expression with ExecuteSQL Function and Embedded
    SELECT Statement    
    ExecuteSql ('Numeric', 'SELECT COUNT(*)  
    FROM    
      sys.triggers  
    WHERE          
      is_disabled = 1  
    AND is_ms_shipped = 0  
    and parent_class_desc = 'DATABASE'  
    and name IN (SELECT Target FROM    
      msdb.dbo.PolicyConfiguration WHERE    
      EvalPolicy = 'Database DDL    
      Triggers Enabled' AND IncludeFlag = 1)')  

    I used this expression to build the condition “Required Database DDL Triggers Enabled” for the policy, which Figure 3 shows. Note that the pane in the screenshot reveals just the beginning of the statement.
  5. Create the policy. You create the policy “Database DDL Triggers Enabled” by specifying Check condition, target, server restrictions, and evaluation mode (On demand, for now). You also have the option to enter the description and assigned policy category in the Description tab, which Figure 4 shows.
  6. Script the policy. Script as many of the settings as possible to perform the action again as needed. In my case, running the script that installs all policies becomes part of the SQL Server installation process on each new box. Microsoft provides the ability to script both policies and conditions, but there are a few problems with its built-in tool:
    • The scripting policy doesn’t provide a script of underlying conditions in the same output file, so you need to combine in the final script the output for all three conditions and the policy itself.
    • The generated “drop policy” script doesn’t notice underlying conditions referenced in other policies. This statement applies to conditions used as targets or server restrictions.

Despite these issues, the Microsoft scripting tool is useful. Without it, you’d find it hard to write all the commands that create necessary objects in the correct format.

Enforcing a Policy

Now let’s look at how all policies are enforced. Briefly, we run on schedule a job consisting of two steps: Step one validates each enabled policy on the server. Step two enforces policy violations for each configured policy by executing a stored procedure with multiple CASE statements inside for each policy.

By design, every time a policy is evaluated either on demand or on schedule, SQL Server saves the summary results of the evaluation in a system table, msdb.dbo.syspolicy_policy_execution_history. Additionally, policy failures against a particular target are saved in another system table, msdb.dbo.syspolicy_policy_execution_history_details.

We can analyze policy failures one record at a time and apply actions to fix them. For these purposes, I created a stored procedure called dbo.ApplyPolicies in msdb. This procedure has one parameter:

@StartTime     
datetime

which defines the beginning of the time slot in msdb.dbo.syspolicy_policy_execution_history that keeps the most recent policy evaluation records. This table stores all undeleted results (as many times as we run) for all policy evaluations.

As I wanted only the most recent ones, I moved the records (the most recent policy evaluation results since @StartTime) into two tables that I created earlier: msdb.dbo.PolicyEvaluation and msdb.dbo.PolicyEvaluation_FailureDetails.

Web Listing 2 shows the script of the dbo.ApplyPolicies stored procedure. To save space, only code associated with fixing violations of the policy “Database DDL Triggers Enabled” is shown.

Notes to Web Listing 2 – Stored Procedure dbo.ApplyFixes.

The body of the stored procedure looks like a giant loop. We step through each record in msdb.dbo.PolicyEvaluation_FailureDetails and then find the corresponding fix based on policy name by checking the whole bunch of IF statements:

IF @EvalPolicy = 'Database DDL Triggers Enabled' ……………do something

Code that enforces each individual policy looks similar to the one presented for “Database DDL Triggers Enabled” policy:

  • I parse the target column to extract the value of object name (in this case, it is a database name) which failed the policy. The full value of the target column in this case has the following format:
  • SQLSERVER:\SQL\YourServerName\YourInstanceName\Databases\YourDatabaseName
  • I create a temporary table #triggers with a list of disabled user-created triggers, not specified as an exception. The query I used here is similar to the one I used to define Check condition “Required Database DDL Triggers Enabled” (see:
    SELECT name FROM sys.triggers WHERE is_disabled = 1 AND is_ms_shipped = 0 and parent_class_desc = 'DATABASE'
    and name IN (SELECT Target FROM msdb.dbo.PolicyConfiguration WHERE EvalPolicy = 'Database DDL Triggers Enabled'
    AND IncludeFlag = 1)
  • I populate the name of the disabled trigger in the Result column of the msdb.dbo.PolicyEvaluation_FailureDetails table by updating the default value that was imported from the system table msdb.dbo.syspolicy_policy_execution_history_details. Originally this column had a number of disabled triggers in the current database. If we have more than one affected disabled trigger, we insert additional rows into our msdb.dbo.PolicyEvaluation_FailureDetails table with the name(s) of each additional disabled trigger.
  • The following fixes will be applied only if the evaluation mode for this policy is set to ‘Enforce Policy’
  • Then, in the internal loop, for each disabled trigger (not listed as exception) we change its status to “enabled” by constructing dynamic SQL and executing it. Results of execution are inserted into the corresponding row in the msdb.dbo.PolicyEvaluation_FailureDetails table.
  • I drop the temporary table #triggers
  • After completing the external loop for each policy failure, I update the status of the column FixFlag in our msdb.dbo.PolicyEvaluation table. Its value becomes 1 if a fix was successfully applied to each affected trigger. It will remain a 0 if for at least one trigger a change of status failed.
Web Listing 2: Stored Procedure dbo.ApplyFixes
USE msdb
GO
IF OBJECT_ID ( 'dbo.ApplyPolicies', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.ApplyPolicies
GO
Create Procedure dbo.ApplyPolicies
(
@StartTime    datetime
,@ApplyFix    int    = 1
)
AS
/*
If @ApplyFix = 1, SP will try to apply policy fix
If @ApplyFix = 0, SP will not try to apply policy fix
*/
SET NOCOUNT ON;
DECLARE       @failure_id          int
,@rc                 int
,@EvalPolicy  varchar(500)
,@Target             nvarchar(400)
,@EvalResults xml
,@EvalDateTime       datetime
,@NoAction           varchar(100)
,@NeedRestart varchar(100)
,@Applied            varchar(100)
,@Disabled           varchar(100)
,@temp               nvarchar(max)
,@temp2                    nvarchar(max)
,@DB_Name            nvarchar(128)
,@obj_name           nvarchar(300)
,@Login                    nvarchar(128)
,@type               varchar(5)
,@Permission  nvarchar(128)
,@ParmDef            nvarchar(500)
,@rc2                int
,@i2                 int
,@failure_id2 int
,@EvaluationMode int
BEGIN TRY
SELECT @NoAction            = 'No Action Taken'
,@Applied            = 'Policy Applied'
,@NeedRestart = 'Change Applied. The setting takes effect
  after the server is restarted'
,@Disabled           = 'Login Disabled'
TRUNCATE TABLE dbo.PolicyEvaluation_FailureDetails;
TRUNCATE TABLE dbo.PolicyEvaluation;
PRINT  CONVERT(varchar(30), getdate(),121) + '  Truncated
  PolicyEvaluation Tables'
IF EXISTS(SELECT 1 FROM dbo.syspolicy_policy_execution_history WHERE
  start_date >= @StartTime)
BEGIN
WITH LatestEval AS
(
SELECT
history_id,
policy_id,
rn = ROW_NUMBER() OVER (PARTITION BY policy_id ORDER BY start_date Desc)
FROM
dbo.syspolicy_policy_execution_history
WHERE
start_date >= @StartTime   --'10/17/2008'
)
INSERT INTO dbo.PolicyEvaluation
(\[rec_id\]
,\[history_id\]
,\[policy_id\]
,\[EvalPolicy\]
,\[EvalDateTime\]
,\[SuccessFlag\]
,\[FixFlag\]
,\[ErrorMsg\])
SELECT
\[rec_id\]             = ROW_NUMBER() OVER (ORDER BY p.name Asc),
\[history_id\]  = s.history_id,
\[policy_id\]          = s.policy_id,
\[EvalPolicy\]  = p.name,
\[EvalDateTime\]       = s.start_date,
\[SuccessFlag\] = s.result,
\[FixFlag\]            = NULL,
\[ErrorMsg\]           = s.exception
FROM
LatestEval    l
INNER JOIN dbo.syspolicy_policy_execution_history s
ON l.history_id = s.history_id
INNER JOIN dbo.syspolicy_policies p
ON s.policy_id = p.policy_id
WHERE
l.rn = 1
PRINT  CONVERT(varchar(30), getdate(),121) + '  Populated Policy
  Evaluation Table'
IF EXISTS (SELECT 1 FROM \[dbo\].\[PolicyEvaluation\] WHERE \[SuccessFlag\] = 0)
BEGIN
INSERT INTO \[dbo\].\[PolicyEvaluation_FailureDetails\]
(\[failure_id\]
,\[EvalPolicy\]
,\[Target\]
,\[EvalDateTime\]
,\[EvalResults\]
,\[FixFlag\]
,\[FixErrorMsg\])
SELECT
\[failure_id\]  = ROW_NUMBER() OVER (ORDER BY p.EvalPolicy,d.detail_id Asc)
,\[EvalPolicy\] = p.EvalPolicy
,\[Target\]            = d.target_query_expression
,\[EvalDateTime\]      = d.execution_date
,\[EvalResults\]       = d.result_detail
,\[FixFlag\]           = NULL
,\[FixErrorMsg\]       = NULL
FROM
dbo.PolicyEvaluation p
INNER JOIN dbo.syspolicy_policy_execution_history_details d
ON p.history_id = d.history_id
WHERE
p.SuccessFlag = 0
SELECT @failure_id   = 1, @rc = @@RowCount
PRINT  CONVERT(varchar(30), getdate(),121) + '  Populated Policy
  Evaluation_FailureDetails Table'
IF @ApplyFix = 1
BEGIN
PRINT  ' '
PRINT  CONVERT(varchar(30), getdate(),121) + '  Start Processing
  Policy Failures'
WHILE (@failure_id <= @rc)
BEGIN
BEGIN TRY
SELECT        @EvalPolicy          = EvalPolicy
,@Target             = Target
,@EvalResults = EvalResults
,@EvalDateTime       = EvalDateTime
FROM
dbo.PolicyEvaluation_FailureDetails
WHERE
failure_id    = @failure_id
SELECT @EvaluationMode                   = EvaluationMode
FROM   msdb.dbo.PolicyExecution
WHERE  EvalPolicy                        = @EvalPolicy
-----------------------------------------------------------------------
IF @EvalPolicy = 'Server DDL Triggers Enabled'
here is the code that fixes this policy
-----------------------------------------------------------------------
IF @EvalPolicy = 'Database DDL Triggers Enabled'
BEGIN
--SQLSERVER:\SQL\GARYZAIK5\SQL2K8\Databases\Finance
SET @DB_Name  = RIGHT(@Target,CHARINDEX('\',REVERSE(@Target))-1)
IF OBJECT_ID('tempdb..#triggers') IS NOT NULL
DROP TABLE #triggers
CREATE TABLE #triggers (
rn                   int NOT NULL IDENTITY(1,1),
obj_name             nVARCHAR(200),
state_desc    nVARCHAR(10),
Result        xml NULL
)
SELECT @temp = 'USE ' + @DB_Name + '
INSERT #triggers (obj_name, state_desc)
SELECT
obj_name      = name,
state_desc    = CASE WHEN is_disabled = 1
  THEN ''DISABLED'' ELSE ''ENABLED'' End
from
sys.triggers
where
is_disabled = 1
AND is_ms_shipped = 0
AND parent_class_desc = ''DATABASE''
AND name IN (SELECT Target FROM msdb.dbo.PolicyConfiguration
  WHERE EvalPolicy = '
  'Database DDL Triggers Enabled'' AND IncludeFlag = 1)
'
EXEC (@temp) ;
SELECT @rc2 = (SELECT COUNT(*) FROM #triggers)
SET           @i2 = 1 ;
WITH temp AS (
SELECT rn,
res = (SELECT * FROM #triggers w WHERE w.rn = t.rn for xml auto )
FROM #triggers t)
UPDATE t
SET           Result = e.res
FROM   #triggers t,
temp e
WHERE  t.rn= e.rn
UPDATE dbo.PolicyEvaluation_FailureDetails
SET           Target        = @DB_Name + ': ' + l.obj_name,
EvalResults   = l.Result
FROM   #triggers     l
WHERE  failure_id    = @failure_id
AND           l.rn          = 1
SELECT @failure_id2 = ISNULL((SELECT MAX(failure_id)
FROM msdb.dbo.PolicyEvaluation_FailureDetails),0)
INSERT msdb.dbo.PolicyEvaluation_FailureDetails (failure_id,EvalPolicy,
  Target,EvalDateTime,EvalResults)
SELECT failure_id    = @failure_id2 + rn - 1,
EvalPolicy    = @EvalPolicy,
Target        = @DB_Name + ': ' + l.obj_name,
EvalDateTime= @EvalDateTime,
EvalResults   = l.Result
FROM   #triggers l
WHERE  rn                   > 1
WHILE (@i2 <= @rc2)
BEGIN
SELECT @obj_name = obj_name FROM #triggers WHERE rn = @i2
IF @EvaluationMode = 0
BEGIN
IF @i2 > 1
UPDATE dbo.PolicyEvaluation_FailureDetails
SET           FixFlag              = 0,
FixErrorMsg   = @NoAction
WHERE  failure_id    = @failure_id2 + @i2 - 1
ELSE
UPDATE dbo.PolicyEvaluation_FailureDetails
SET           FixFlag              = 0,
FixErrorMsg   = @NoAction
WHERE  failure_id    = @failure_id
END
ELSE
BEGIN
SET @temp            = N'USE ' + @DB_Name + ';
ENABLE Trigger ' +  @obj_name + N' ON DATABASE'
SET @ParmDef  = N'@DB_Name nvarchar(128), @obj_name nvarchar(300)'
EXEC sp_executesql @temp, @ParmDef, @DB_Name, @obj_name
IF @i2 > 1
UPDATE dbo.PolicyEvaluation_FailureDetails
SET           FixFlag              = 1,
FixErrorMsg   = @Applied
WHERE  failure_id    = @failure_id2 + @i2 - 1
ELSE
UPDATE dbo.PolicyEvaluation_FailureDetails
SET           FixFlag              = 1,
FixErrorMsg   = @Applied
WHERE  failure_id    = @failure_id
END
SET @i2 = @i2 + 1
END
DROP TABLE #triggers
END
-------------------------------------------------------------
END TRY
BEGIN CATCH
DECLARE @ErrorNumber int,
@ErrorSeverity       int,
@ErrorState          int,
@ErrorProcedure      sysname,
@ErrorLine           int,
@ErrorMessage nvarchar(max)
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorLine = ERROR_LINE()
,@ErrorMessage = ERROR_MESSAGE();
PRINT 'Error ' + CONVERT(varchar(50), @ErrorNumber) +
', Severity ' + CONVERT(varchar(5), @ErrorSeverity) +
', State ' + CONVERT(varchar(5), @ErrorState) +
', Procedure ' + ISNULL(@ErrorProcedure, '-') +
', Line ' + CONVERT(varchar(5), @ErrorLine);
PRINT @ErrorMessage;
UPDATE dbo.PolicyEvaluation_FailureDetails
SET           FixFlag              = 0,
FixErrorMsg   = @ErrorMessage
WHERE  failure_id    = @failure_id
END CATCH
SET @failure_id = @failure_id + 1
END ;
WITH FixResults AS (
SELECT EvalPolicy,
FixFlag       = MIN(FixFlag)
FROM   dbo.PolicyEvaluation_FailureDetails
GROUP BY
EvalPolicy
)
UPDATE dbo.PolicyEvaluation
SET           FixFlag              = f.FixFlag
FROM   FixResults    f
WHERE  PolicyEvaluation.EvalPolicy = f.EvalPolicy
END
END
END
PRINT  CONVERT(varchar(30), getdate(),121) + '
*** The End ***'
----------------------------------------------------------
END TRY
BEGIN CATCH
PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
', State ' + CONVERT(varchar(5), ERROR_STATE()) +
', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +
', Line ' + CONVERT(varchar(5), ERROR_LINE());
PRINT ERROR_MESSAGE();
END CATCH
GO

The Company wanted to run all policy evaluations at the same time: every Sunday. So I created Verify_Policies_Schedule (see Web Listing 3) and associated it with all created policies.

Web Listing 3: Create Verify_Policies_Schedule
PRINT  'CREATING SCHEDULE FOR POLICY EVALUATION'
BEGIN TRANSACTION

BEGIN TRY
       Declare       @ReturnCode int,
                           @schedule_uid uniqueidentifier,
                           @schedule_id int,
                           @pol_id int

       SELECT @schedule_uid = schedule_uid FROM msdb.dbo.sysschedules
  _localserver_view WHERE name = N'Verify_Policies_Schedule'
       IF  @schedule_uid IS NULL
       BEGIN
              SELECT @ReturnCode = 0
              EXEC @ReturnCode =  msdb.dbo.sp_add_schedule
                     @schedule_name                    = N'Verify_Policies
  _Schedule'
                     ,@enabled                         = 1
                     ,@freq_type                       = 8    --weekly
                     ,@freq_interval                   = 1    --Sunday
                     ,@freq_subday_type         = 1    --at the specific time
                     ,@freq_subday_interval            = 0          
                     ,@freq_relative_interval   = 0
                     ,@freq_recurrence_factor   = 1    --once a week
                     ,@active_start_date        = 20080101
                     ,@active_end_date          = 99991231
                     ,@active_start_time        = 500         --00:05:00
                     ,@active_end_time          = 235959
                     ,@owner_login_name         = 'sa'
                     ,@schedule_uid                    = @schedule_uid OUTPUT
                     ,@schedule_id              = @schedule_id OUTPUT
           
              IF (@ReturnCode <> 0)
              BEGIN
                     PRINT  'COULD NOT CREATE SCHEDULE. TRANSACTION
  ROLLED BACK'
                     GOTO QuitWithRollback
              END
       END  

       SELECT @pol_id = policy_id FROM msdb.dbo.syspolicy_policies WHERE
  name = N'Database DDL Triggers Enabled'
       IF @pol_id IS NOT NULL
              EXEC msdb.dbo.sp_syspolicy_update_policy
                     @policy_id                        = @pol_id,
                     @execution_mode                   = 4,
                     @is_enabled                       = True,
                     @schedule_uid              = @schedule_uid
/* Here you would insert similar code for other policies */
       COMMIT TRAN
       PRINT  'SUCCESSFULLY CHANGED EVALUATION MODE FOR ALL POLICIES
  TO On Schedule'
           
END TRY
BEGIN CATCH
       PRINT  'COULD NOT CREATE SCHEDULE. TRANSACTION ROLLED BACK'
       PRINT  ''
       SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH    
QuitWithRollback:  
GO

After changing the evaluation mode for all policies to On schedule and associating them with Verify_Policies_Schedule, I created Verify_Policies_Job. This job consists of two steps:

  1. Check the policy store on each server and evaluate all scheduled policies by running the Windows PowerShell command
    Invoke-PolicyEvaluation
    For a description of this command, see the Microsoft article "Using the Invoke-PolicyEvaluation cmdlet."
     
  2. Fix the problems (if possible) by executing the stored procedure dbo.ApplyFix, which Web Listing 1 shows.

Web Listing 4 shows the script that creates the Verify_Policies job. There are a few problems with this script:

  • It isn’t flexible—the same Verify_Policies job must be executed on each SQL Server 2008 instance. In the presented variant, when the engineering team configured a new instance of SQL Server, they had to add flexibility to search the proper local Policy store.
  • The schedule UID for Verify_Policies_Schedule is hardcoded for now (it references Verify_Policies_Schedule), but it will be different every time a DBA installs the scripts on another server. We need to add flexibility by evaluating the policies associated with the schedule name (Verify_Policies_Schedule), not the system-generated UID.
Web Listing 4: Code to create Verify Policies job
USE \[msdb\]
GO
BEGIN TRAN
DECLARE @ReturnCode INT, @schedule_uid uniqueidentifier, @schedule_id int
SELECT @ReturnCode = 0

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Verify_Policies')
BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_delete_job @job_name=N'Verify_Policies',
  @delete_unused_schedule=0
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'\[Uncategorized
  (Local)\]' AND category_class=1)
BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL',
  @name=N'\[Uncategorized (Local)\]'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Verify_Policies',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'\[Uncategorized (Local)\]',
  @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
    @job_name = N'Verify_Policies',
    @server_name = @@ServerName
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  

/******   Step \[Evaluate policies.\]     ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Evaluate
  policies',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=4,  --go to step 2
  @on_success_step_id=2,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'PowerShell',
  @command=N'dir SQLSERVER:\SQLPolicy\MyComputer\MyInstance\
  Policies | where \{ $_.ScheduleUid -eq "B1594BBB-269C-4BDB-9866-C0CD8A7AE694"  
  \} |  where \{ $_.Enabled -eq 1\} | where \{$_.AutomatedPolicyEvaluationMode
  -eq 4\} | Invoke-PolicyEvaluation -AdHocPolicyEvaluationMode 2 -TargetServerName
  MyComputer\MyInstance',
  @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/******   Step \[Fix the Problems\]     ******/
DECLARE @command2 nvarchar(max)
SELECT @command2 = N'EXEC dbo.ApplyPolicies ''' + CONVERT(varchar(30),
  getdate(),120) + ''''

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Fix
  the Problems (if possible).',
  @step_id=2,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command= @command2,
  @database_name=N'msdb',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  

COMMIT TRANSACTION
PRINT 'JOB Verify_Policies WAS CREATED SUCCESSFULLY'
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0)
BEGIN
 ROLLBACK TRANSACTION
 PRINT 'COULD NOT CREATE JOB Verify_Policies'
END
EndSave:

GO

There’s no problem in dynamically re-defining the content of the T-SQL step inside the job. Unfortunately, the job step, which is based on a PowerShell command, must be evaluated before the first job step starts.

I needed another job that would properly reconfigure Step 1 of the Verify_Policies job, then call this job with new, properly defined parameters. Web Listing 5 shows the script that creates another Configure_Verify_Policies job.

Web Listing 5
USE \[msdb\]
GO
BEGIN TRAN
DECLARE @ReturnCode INT, @schedule_uid uniqueidentifier,
  @schedule_id int, @dummy_job_id uniqueidentifier
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE
  name=N'\[Uncategorized (Local)\]' AND category_class=1)
BEGIN
       EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB',
  @type=N'LOCAL', @name=N'\[Uncategorized (Local)\]'
       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)

SELECT @schedule_uid = schedule_uid FROM msdb.dbo.sysschedules
  _localserver_view WHERE name = N'Verify_Policies_Schedule'
SELECT @dummy_job_id = job_id FROM msdb.dbo.sysjobs_view WHERE name
  = 'syspolicy_check_schedule_' + CONVERT(char(36),@schedule_uid)
IF @dummy_job_id IS NOT NULL
BEGIN
       EXEC @ReturnCode =  msdb.dbo.sp_update_job @job_id=@dummy_job_id,
                     @new_name = N'Configure_Verify_Policies',
                     @enabled=1,
                     @notify_level_eventlog=0,
                     @notify_level_email=0,
                     @notify_level_netsend=0,
                     @notify_level_page=0,
                     @delete_level=0,
                     @description=N'No description available.',
                     @category_name=N'\[Uncategorized (Local)\]',
                     @owner_login_name=N'sa'
                   
       SET @jobId=@dummy_job_id
END
ELSE
BEGIN
       SELECT @jobId = job_id FROM msdb.dbo.sysjobs_view WHERE
  name = N'Configure_Verify_Policies'
       IF @jobId IS NOT NULL
              EXEC @ReturnCode =  msdb.dbo.sp_update_job @job_id=@jobId,
                           @enabled=1,
                           @notify_level_eventlog=0,
                           @notify_level_email=0,
                           @notify_level_netsend=0,
                           @notify_level_page=0,
                           @delete_level=0,
                           @description=N'No description available.',
                           @category_name=N'\[Uncategorized (Local)\]',
                           @owner_login_name=N'sa'
       ELSE
              EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=
  N'Configure_Verify_Policies',
                     @enabled=1,
                     @notify_level_eventlog=0,
                     @notify_level_email=0,
                     @notify_level_netsend=0,
                     @notify_level_page=0,
                     @delete_level=0,
                     @description=N'No description available.',
                     @category_name=N'\[Uncategorized (Local)\]',
                     @owner_login_name=N'sa',
                     @job_id = @jobId OUTPUT

        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobservers WHERE job_id = @jobId)
BEGIN
       EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
              @job_name = N'Configure_Verify_Policies',
              @server_name = @@ServerName
       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
 
Declare @steps int, @i int
select @steps = COUNT(*) from dbo.sysjobsteps s, dbo.sysjobs j
  where s.job_id = j.job_id and j.name = N'Configure_Verify_Policies'
select @i = @steps
WHILE (@i > 0)
BEGIN
       EXEC dbo.sp_delete_jobstep
              @job_name = N'Configure_Verify_Policies',
              @step_id = @i ;
 
       SET @i = @i - 1
END

/******   Step \[Kill Running Job\]     ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
  @step_name=N'Kill Running Job',
              @step_id=1,
              @cmdexec_success_code=0,
              @on_success_action=4,
              @on_success_step_id=2,
              @on_fail_action=2,
              @on_fail_step_id=0,
              @retry_attempts=0,
              @retry_interval=0,
              @os_run_priority=0, @subsystem=N'TSQL',
              @command=N'
IF EXISTS (SELECT j.\[name\]
              from msdb.dbo.sysjobactivity a
              join msdb.dbo.sysjobs j
              on j.job_id=a.job_id and j.name = ''Verify_Policies''
              where a.start_execution_date is not null
              and a.job_history_id is null)
       EXEC msdb.dbo.sp_stop_job N''Verify_Policies''
              ',
              @database_name=N'msdb',
              @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
/******   Step \[Configure ScheduleUid\]     ******/
Declare @StepString  nvarchar(max)
SET           @StepString = N'DECLARE    @TargetSrv    sysname,
  @ScheduleUid uniqueidentifier, @sScheduleUid varchar (40),
  @CommandString nvarchar(max)
Declare       @DataLocation varchar(1000), @OutputFile varchar(1000)
exec master.dbo.xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''Software\Microsoft\MSSQLServer\SQLServerAgent'', N''ErrorLogFile'',
  @DataLocation OUTPUT
SELECT @DataLocation = LEFT(@DataLocation,LEN(@DataLocation)-CHARINDEX(''\'',REVERSE(@DataLocation)))
SET           @OutputFile          = @DataLocation + ''\Verify_Policies.log''
 
SELECT @TargetSrv = @@ServerName
SELECT @ScheduleUid = schedule_uid FROM msdb.dbo.sysschedules
  _localserver_view WHERE name = ''Verify_Policies_Schedule''
SELECT @sScheduleUid= CONVERT(nchar(36),@ScheduleUid)
IF CHARINDEX(''\'',@TargetSrv) > 0
       SELECT @CommandString = N''dir SQLSERVER:\SQLPolicy\'' +
  @TargetSrv + ''\Policies | where \{ $_.ScheduleUid -eq "'' +
  @sScheduleUid + ''" \} |  where \{ $_.Enabled -eq 1\} | where
  \{$_.AutomatedPolicyEvaluationMode -eq 4\} | Invoke-PolicyEvaluation
  -AdHocPolicyEvaluationMode 2 -TargetServerName '' + @TargetSrv
ELSE
       SELECT @CommandString = N''dir SQLSERVER:\SQLPolicy\'' +
  @TargetSrv + ''\DEFAULT\Policies | where \{ $_.ScheduleUid -eq "'' +
  @sScheduleUid + ''" \} |  where \{ $_.Enabled -eq 1\} | where
  \{$_.AutomatedPolicyEvaluationMode -eq 4\} | Invoke-PolicyEvaluation
  -AdHocPolicyEvaluationMode 2 -TargetServerName '' + @TargetSrv
 
EXEC msdb.dbo.sp_update_jobstep
       @job_name = ''Verify_Policies''
       ,@step_id = 1
       ,@command = @CommandString
     
SELECT @CommandString = N''EXEC dbo.ApplyPolicies '''''' + CONVERT
  (varchar(30),getdate(),120) + '''''',1''
EXEC msdb.dbo.sp_update_jobstep
       @job_name = ''Verify_Policies''
       ,@step_id = 2
,@output_file_name=@OutputFile  
       ,@command = @CommandString        '
 
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=
  N'Configure ScheduleUid',
              @step_id=2,
              @cmdexec_success_code=0,
              @on_success_action=4,
              @on_success_step_id=3,
              @on_fail_action=2,
              @on_fail_step_id=0,
              @retry_attempts=0,
              @retry_interval=0,
              @os_run_priority=0, @subsystem=N'TSQL',
              @command=@StepString,
              @database_name=N'msdb',
              @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
/******   Step \[Run Verify_Policies Job\]     ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
              @step_name=N'Run Verify_Policies Job',
              @step_id=3,
              @cmdexec_success_code=0,
              @on_success_action=1,
              @on_success_step_id=0,
              @on_fail_action=2,
              @on_fail_step_id=0,
              @retry_attempts=0,
              @retry_interval=0,
              @os_run_priority=0, @subsystem=N'TSQL',
              @command=N'EXEC dbo.sp_start_job N''Verify_Policies'' ',
              @database_name=N'msdb',
              @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
EXEC @ReturnCode = msdb.dbo.sp_attach_schedule
   @job_name = N'Configure_Verify_Policies',
   @schedule_name = N'Verify_Policies_Schedule' ;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
COMMIT TRANSACTION
PRINT  'JOB Configure_Verify_Policies WAS CREATED SUCCESSFULLY'
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0)
BEGIN
       ROLLBACK TRANSACTION
       PRINT  'COULD NOT CREATE JOB Configure_Verify_Policies'
END
EndSave:
GO

This job prepares the correct content of the steps in the following Verify_Policies job and immediately starts that job. Additionally, we want to make sure that at the time when we start the Configure_Verify_Policies job, no other instances of Verify_Policies jobs are running. Otherwise, we might not be able to uniquely identify the results of the last policy evaluation in the system tables.

However, instead of creating a new job, I decided to use the already existing dummy job. The first time a user sets the evaluation mode of any policy to On schedule, SQL Server by design creates this dummy job with a name starting with syspolicy_check_schedule_.

At the same time, you can’t delete this job unless there’s at least one scheduled policy in the system. So instead of keeping a useless job in the system, I renamed it Configure_Verify_Policies.

I deleted all the steps originally generated by SQL Server and dynamically redefined all the steps in this job. The Configure_Verify_Policies job consists of three steps, which you can also see in Web Listing 5:

  1. Kill running job—In this step I define and execute dynamic T-SQL that checks if there’s a running instance of the Verify_Policies job and, if needed, kills the job.
     
  2. Configure ScheduleUid—In this step, I do five things:
    • Find the UID for Verify_Policies_Schedule
    • Find the actual server and instance name
    • Finish building the dynamic SQL for the @command parameter of the msdb.dbo.sp_update_jobstep stored procedure and execute it; this changes the content of the PowerShell script needed to execute in step 1 of the Verify_Policies job.
    • Define the location of the job’s log file. The Company requested that I provide a log file for the DBA to track the steps of the execution of the ApplyFix stored procedure for debugging purposes. We decided to put the job’s log file in the same folder as the SQL Server Agent’s log.
    • Using the same technique as above, we then build and execute the dynamic SQL to assign output from Step 2 in the Verify_Policies job to log the file in the proper location.
    • Run the Verify_Policies job—In this step, we actually start the “updated” Verify_Policies job by invoking the sp_start_job stored procedure. At the end of the script, I associated the Configure_Verify_Policies job with Verify_Policies_Schedule.

Purging the Results of a Policy Evaluation

As I mentioned above, the results of each policy evaluation (scheduled or running on-demand from SQL Server Management Studio—SSMS) are stored in system tables. SQL Server 2008 installs the preconfigured system job syspolicy_purge_history that’s supposed to clean those tables.

This job must run periodically to remove aged data from the msdb.dbo.syspolicy_policy_execution_history table and the msdb.dbo.syspolicy_policy_execution_history_details table. Upon installation, the job is configured to keep all data. The code in Listing 5 would run to enable syspolicy_purge_history job and purge the policy evaluation results after 15 days of storage.

Listing 5: Code to Purge Policy Evaluation Results After 15 Days
BEGIN TRANSACTION  
DECLARE @ReturnCode INT  
EXEC msdb.dbo.sp_syspolicy_configure @name=    
  Enabled, @value=1  
EXEC msdb.dbo.sp_syspolicy_configure @name=    
  N'HistoryRetentionInDays', @value=15  
EXEC @ReturnCode =  msdb.dbo.sp_update_job    
  @job_name=N'syspolicy_purge_history',    
  @enabled=1  
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO    
  QuitWithRollback  
COMMIT TRANSACTION  
GOTO EndSave  
QuitWithRollback:      
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION  
EndSave:

You can use SSMS to change the default schedule for this job and the length of the storage interval, which Figure 5 shows. To change history retention, go to Management, Policy Management, and right-click Properties, then change the value of the HistoryRetentionInDays parameter.

Solution Management

The simplest way to include (or exclude) policy from evaluation is enabling (or disabling) it. Use SSMS to exclude a policy from the validation process. Go to Management, Policy Management, Policies, and select Policy, then right-click Enable (or Disable, as the case may be).

To add exceptions to the policy validation process, you need to add at least one record to the dbo.PolicyConfiguration table and use a value of 2 for the IncludeFlag column. In some cases you can specify additional elements to include in the policy validation process.

These inclusions should have a value of 1 in the IncludeFlag column. For example, to include a database DDL trigger in the validation process, use the following command:

INSERT dbo.PolicyConfiguration (EvalPolicy,    
Target, IncludeFlag)    
VALUES (‘Database DDL Triggers Enabled’,    
‘some_trigger_name’, 1)

As I discussed earlier, policies can run either in Display Only or Enforce Policy modes. To change the evaluation mode for a policy, set the corresponding value of the column EvaluationMode in the table msdb.dbo.PolicyExecution. For example, to run the policy “Database DDL Triggers Enabled” in Display Only mode, use the following code:

UPDATE msdb.dbo.PolicyExecution  
SET EvaluationMode = 0   
WHERE EvalPolicy= ‘Database DDL    
Triggers Enabled’

To view the results of policy execution, run the following commands:

select * from msdb.dbo.PolicyEvaluation    
order by EvalPolicy  select * from msdb.dbo.PolicyEvaluation    
_FailureDetails order by EvalPolicy, Target

The previous results of policy execution in those two tables are truncated at the beginning of each run of the Configure_Verify_Policies job. Both tables refresh with the most recent results of policy evaluation.

Successfully run policies will have a value of 1 in the SuccessFlag column in the msdb.dbo.PolicyEvaluation table. Policy violations are listed in the msdb.dbo.PolicyEvaluation_FailureDetails table.

If a policy runs in the EnforcePolicy mode, the results of policy enforcement will be in the FixFlag column. Positive values in this column mean a successful fix of a policy violation.

A log file is generated every time a Configure_Verify_Policies job runs. The Log file Verify_Policies.txt is located in the same folder as SQL Server Agent logs (for example, C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\Log).

You must have proper OS permissions to view the log file. Additionally, you can build a notification process if a process finds policy violations by analyzing the value of the SuccessFlag column in the msdb.dbo.PolicyEvaluation table.

Solving the Limitations of SQL Server Policy-Based Management

This solution provided DBAs with a tool that simultaneously enforced common policies across all managed SQL Server instances and allowed DBAs to enter exceptions if an application had special requirements. Last, but not least, the engineering team acquired the methodology to expand existing sets of policies.