SQL SERVER – Finding What Policies Are Enabled on Our Databases

When I wrote about Policy Management last week (SQL SERVER – Introduction to Policy Management), lesser did I know I will get queries from blog readers on the basics. As I always said, Policy Management is always an underappreciated capability inside SQL Server. In one of the mails, I was asked – “How do I know which policy is enabled on my Server?” Before I can answer the same with a simple query to our metadata, let me walk through what happens in words before going into the solution.

When SQL Server “policy engine” executes a policy, it determines which objects to evaluate against the policy’s condition. It uses the target set and target set level data to determine this. The information can be viewed in the syspolicy_target_sets and syspolicy_target_set_level views. A target set has data for the target type (for example, [Database]) and target skeleton (for example, [Server/Database]). This target skeleton includes all databases on the server. The target set can be further narrowed with a filter. This filter can be another condition. This is the data stored in the target set level. Below is a query to help see this metadata.

/* Query the metadata to find the Policy, Condition, Target Set informations on our database */
SELECT p.policy_id,
p.is_enabled,
p.name AS 'policy_name',
c.condition_id,
c.name AS 'condition_name',
c.expression AS 'condition_expression',
ts.target_set_id,
ts.TYPE,
ts.type_skeleton,
tsl.condition_id AS 'target_set_condition_id'
FROM msdb.dbo.syspolicy_policies p
INNER JOIN msdb.dbo.syspolicy_conditions c
ON p.condition_id = c.condition_id
INNER JOIN msdb.dbo.syspolicy_target_sets ts
ON ts.object_set_id = p.object_set_id
INNER JOIN msdb.dbo.syspolicy_target_set_levels tsl
ON ts.target_set_id = tsl.target_set_id
-- WHERE p.is_enabled <> 0 -- Use this to get only enabled Policies on the DB

If you plan to run these on a SQL Server 2012+ server box, then you are likely to see the AlwaysOn related policies for reference. Go ahead and uncomment the last line to see only policies that are enabled on your SQL Server box. I would be curious to know how many of you actively use this capability and for what reasons. Do let me know via your comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Introduction to Policy Management

Group Policy Management SQL SERVER   Introduction to Policy ManagementAs March comes, most of us are trying to chase our dreams and hope the much awaited yearend review comes good for us. I know many pray and don’t realize it is just an assessment of what was done the whole of last year. It is not a myopic view, but a view that is based of rules on how consistent we have been the complete year. These rules and policy decisions are generally governed by how our organizations work and how policy decisions are made in each of the region where you work. Though we understand how these policies work, we never look at the policy as a guiding rail for our superiors to work. In the same context, I wish all of us also understand the nuances of policy management with SQL Server too.

SQL Server 2008 introduced this neat feature which is lesser appreciated by many in the SQL Server world. This blog is to set some context into what are the building blocks of SQL Server Policy Management. If we understand these, then in the future we will build on some real world examples.

Target Set

Targets are entities that can be managed by the Policy Based Management. All similar type targets within an instance of SQL Server form a target hierarchy. A target filter is then applied to all targets within the target hierarchy to form a target set. The Policy Based Management engine, which is implemented in SQL CLR, will traverse the target set and compare the policy’s condition to each target in the target set to determine compliance or a violation.

Facet

A facet has properties that model the behavior or characteristics of managed targets. The properties are built into the facet and are determined by the facet developer. SQL Server currently supports Microsoft built-in facets.

Condition

A condition is a Boolean expression that sets allowed states by the managed target for a particular facet property. A condition must evaluate to either true or false. A condition can include multiple properties of a facet that are and\or together.

Policy

A policy includes a condition based on facet properties that are evaluated against a set of targets. A policy can only have one condition. The policy is executed with one of the evaluation modes which determines how it is implemented and if it logs or prevents out of compliance violations.

Evaluation Mode

Either the On Demand evaluation mode can check the validity of the policy or it can configure the target to adhere to the policy. Not all facet properties are settable. Some are read-only. These read-only properties cannot be configured.

The Check On Change (prevent out of compliance) evaluation mode uses a DDL Server trigger to enforce a policy. When the policy is created, a DDL Server trigger is updated to fire on create, drop or alter activity that starts the policy execution. If the policy is violated, it will roll back the transaction which prevents the change.

The Check On Change (log out of compliance) evaluation mode uses event notification and service broker to log a policy violation. When the policy is created, an event notification is updated to capture create, drop or alter activity which starts the policy execution. If the policy is violated, it will log the policy violation along with information to the Policy Based Management execution history and health state tables in msdb.

The Check on Schedule (log out of compliance) evaluation mode uses a SQL Server Agent job and schedule to start the policy execution. If the policy is violated, it will log the policy violation along with information to the Policy Based Management execution history and health state tables in msdb.

Subscriptions

A subscription allows a database to subscribe to a policy group. All of the policies within the policy group will be applied to a single database. Policy groups and subscriptions make it easier to manage a large group of policies for a database.

Now that we have got the basics cleared out. In subsequent blog posts, we will take examples of each of above building blocks and bring some real world use cases. As readers, I would like to know how many of you have been using Policy Management in your environments today. Can you share few examples where you found it useful?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Identifying guest User using Policy Based Management

If you are following my recent blog posts, you may have noticed that I’ve been writing a lot about Guest User in SQL Server. Here are all the blog posts which I have written on this subject:

One of the requests I received was whether we could create a policy that would prevent users unable guest user in user databases. Well, here is a quick tutorial to answer this. Let us see how quickly we can do it.

Requirements

  1. Check if the guest user is disabled in all the user-created databases.
  2. Exclude master, tempdb and msdb database for guest user validation.

We will create the following conditions based on the above two requirements:

  1. If the name of the user is ‘guest’
  2. If the user has connect (@hasDBAccess) permission in the database
  3. Check in All user databases, except: master, tempDB and msdb

Once we create two conditions, we will create a policy which will validate the conditions.

Condition 1: Is the User Guest?

Expand the Database >> Management >> Policy Management >> Conditions

pbm1 SQL SERVER   Identifying guest User using Policy Based Management

Right click on the Conditions, and click on “New Condition…”. First we will create a condition where we will validate if the user name is ‘guest’, and if it’s so, then we will further validate if it has DB access.

pbm2 SQL SERVER   Identifying guest User using Policy Based Management

Check the image for the necessary configuration for condition:
Facet: User
Expression:
@Name = ‘guest’

Condition 2: Does the User have DBAccess?

Expand the Database >> Management >> Policy Management >> Conditions

Right click on Conditions and click on “New Condition…”. Now we will validate if the user has DB access.

pbm2 2 SQL SERVER   Identifying guest User using Policy Based Management

Check the image for necessary configuration for condition:
Facet: User
Expression:
@hasDBAccess = False

Condition 3: Exclude Databases

Expand the Database >> Management >> Policy Management >> Conditions

Write click on Conditions and click on “New Condition…” Now we will create condition where we will validate if database name is master, tempdb or msdb and if database name is any of them, we will not validate our first one condition with them.

pbm2 3 SQL SERVER   Identifying guest User using Policy Based Management

Check the image for necessary configuration for condition:
Facet: Database
Expression:
@Name != ‘msdb’
AND @Name != ‘tempdb’
AND @Name != ‘master’

The next step will be creating a policy which will enforce these conditions.

Creating a Policy

Right click on Policies and click “New Policy…”

Here, we justify what condition we want to validate against what the target is.

pbm4 SQL SERVER   Identifying guest User using Policy Based Management

Condition: Has User DBAccess
Target Database: Every Database except (master, tempdb and MSDB)
Target User: Every User in Target Database with name ‘guest’

pbm5 SQL SERVER   Identifying guest User using Policy Based Management

Now we have options for two evaluation modes: 1) On Demand and 2) On Schedule

We will select On Demand in this example; however, you can change the mode to On Schedule through the drop down menu, and select the interval of the evaluation of the policy.

Evaluate the Policies

We have selected OnDemand as our policy evaluation mode. We will now evaluate by means of executing Evaluate policy. Click on Evaluate and it will give the following result:

pbm6 SQL SERVER   Identifying guest User using Policy Based Management

The result demonstrates that one of the databases has a policy violation. Username guest is enabled in AdventureWorks database. You can disable the guest user by running the following code in AdventureWorks database.

pbm7 SQL SERVER   Identifying guest User using Policy Based Management

USE AdventureWorks;
REVOKE CONNECT FROM guest;

Once you run above query, you can already evaluate the policy again. Notice that the policy violation is fixed now.

pbm9 SQL SERVER   Identifying guest User using Policy Based Management

You can change the method of the evaluation policy to On Schedule and validate policy on interval. You can check the history of the policy and detect the violation.

pbm10 SQL SERVER   Identifying guest User using Policy Based Management

pbm11 SQL SERVER   Identifying guest User using Policy Based Management

Quiz

I have created three conditions to check if the guest user has database access or not. Now I want to ask you: Is it possible to do the same with 2 conditions? If yes, HOW? If no, WHY NOT?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Policy Based Management – Create, Evaluate and Fix Policies

Introduction

This article will cover the most spectacular feature of SQL 2008 – Policy-based management and how the configuration of SQL Server with policy-based management architecture can make a powerful difference. Policy based management is loaded with several advantages. It can help you implement various policies for reliable configuration of the system. It also provides additional administration assistance to DBAs and helps them effortlessly manage various tasks of SQL Server across the enterprise.

Basics of Policy Management

SQL server 2008 has introduced policy management framework, which is the latest technique for SQL server database engine. SQL policy administrator uses SQL Server Management Studio to create policies that can handle entities on the server side like the SQL Server objects and the instance of SQL Server databases. It consists of three components: policy administrators (who create policies), policy management, and explicit administration. Policy-based management in SQL Server assists the database administrators in defining and enforcing policies that tie to database objects and instances. These policies allow the administrator to configure and manage SQL server across the enterprise.

The following advantages can be achieved by appropriate administration of policy management system.

  • It interacts with various policies for successful system configuration.
  • It handles the changes in the systems that are the result of configuration against authoring policies.
  • It reduces the cost of ownership with simple elaboration of administration tasks.
  • It detects various compliance issues in SQL Server Management Studio.

Policy Management Terms

To have a better grip on the concept of Policy-based management there are some key terms you need to understand.

  • Target – A type of entity that is appropriately managed by Policy-based management. For example, a table, database and index, to name a few.
  • Facet -A property that can be managed in policy-based management. A clear example of facet is the name of Trigger or the Auto Shrink Property of database.
  • Conditions – Criteria that specifies the state of facet to true or false. For example, you can adjust the state of a facet that gives you clear specifications of all stored procedures in the Schema “Banking”.
  • Policy – A set of rules specified for the server objects or the properties of database.

Practical Example of Policy Management

Exploring of Facets

Facets are database objects and each of them is a container of one or more database object. First, you need to navigate the object explorer and expand the policy-based management node and the management node. You will see conditions, policies and facets nodes. SQL Server 2008 has many different facets available to use.

PolicyManagement1 SQL SERVER   Policy Based Management   Create, Evaluate and Fix Policies

To view the list of facets, expand the facet node

PolicyManagement2 SQL SERVER   Policy Based Management   Create, Evaluate and Fix Policies

Double click on each of these facets to the list of the facet properties.

PolicyManagement3 SQL SERVER   Policy Based Management   Create, Evaluate and Fix Policies

Let us understand the two next elements of creating condition and creating policy with real life example of Statistics. We will try to create statistic property of the database. We have property of statistic IsAutoCreated. We can set that using database property window under option tab. This property takes two values True or False.

PolicyManagement3a SQL SERVER   Policy Based Management   Create, Evaluate and Fix Policies

We will follow up on this property in different steps. We will first create condition and right after we will use the same condition in a policy. That policy will be evaluated by user. User will have to two options either let us evaluated by scheduled task or fix non complaining policy manually.

Create a Condition

Creation of condition in Policy-based management is the next thing after identifying the problem. In our case, we want to make sure that everywhere IsAutoCreate is set to True everywhere.

PolicyManagement4 SQL SERVER   Policy Based Management   Create, Evaluate and Fix Policies

Each facet displays different kinds of properties. As we are interested in the IsAutoCreated property of the statistics we have to select facet as Statistic.

Create a Policy

Creating a policy is the next important task after creating a condition. The condition has to be created to select the proper property of the object. However, a policy is created to specify the location where the condition has to be applied.

PolicyManagement5 SQL SERVER   Policy Based Management   Create, Evaluate and Fix Policies

Please follow the instructions given in the above image. Make sure to select all target databases. In given example I have two database installed on my server that brings up two different targets servers.

This brings up an interesting concept of targets. Targets are database objects. They can be whole database or single parts of the database. It may be possible that they are different kind of objects but have same kind of properties.

While creating a policy we have kept the evaluation mode as “on demand”, which means that we will be running this policy manually, instead of scheduled job. Scheduled job is good idea to run policy.

Evaluate a Policy

As in the previous step, we have selected to evaluate the policy manually; we will evaluate that using SSMS. Right click on policy brings up with lots of options. Select Evaluate from the menu, this will bring up the following image.

PolicyManagement8 SQL SERVER   Policy Based Management   Create, Evaluate and Fix Policies

On the screen of Evaluate Policies there is a button which suggests evaluating the policies. Once clicked it will give the following kind of screen with a status.

PolicyManagement8a SQL SERVER   Policy Based Management   Create, Evaluate and Fix Policies

You will see a green icon in the image. This icon indicates the policy evaluated the status to True. If you notice you will find that in our example all the policies are complied with.

Fix Non-complying Policy

Now, let us change the IsAutoCreate policy of one of test databases to true and run the evaluation all over again. If you observe you will notice that one of the statuses is marked as false with a little red image on the left.

PolicyManagement9 SQL SERVER   Policy Based Management   Create, Evaluate and Fix Policies

Furthermore, once you select the checkbox and clicked on the Apply button you will find an additional checkbox on the side. This Apply button will raise a popup confirming that the property of the target has been changed to comply with the policy. Next, click Ok to confirm. This will change the properties of the Test database to comply with the new policy on all of our targets specified earlier.

PolicyManagement9a SQL SERVER   Policy Based Management   Create, Evaluate and Fix Policies

PolicyManagement9b SQL SERVER   Policy Based Management   Create, Evaluate and Fix Policies

In our example, we have manually evaluated the policy and fixed its noncompliance. With powershell you can perform the same using SQL Server Agent.

Summary

Policy-Based Management empowers you with greater control over the procedures of database as a Database Administrator. It provides you the ability to enforce paper policies at database level. Paper polices are used as guidelines for understanding database standards. However, it necessitates some skills, time and efforts to enforce these polices. You need to go with a fine toothed comb to enforce these policies. Policy-based management system helps you define these polices and ensure that they will be enforced appropriately.

Reference : Pinal Dave (http://blog.SQLAuthority.com), DNS

SQL SERVER – 2008 – Policy-Based Management – Create, Evaluate and Fix Policies

This article will cover the most spectacular feature of SQL 2008 – Policy-based management and how the configuration of SQL Server with policy-based management architecture can make a powerful difference. Policy based management is loaded with several advantages. It can help you implement various policies for reliable configuration of the system. It also provides additional administration assistance to DBAs and helps them effortlessly manage various tasks of SQL Server across the enterprise.

1 Introduction
2 Basics of Policy Management
3 Policy Management Terms
4 Practical Example of Policy Management
4.1 Exploring of Facets
4.2 Create a Condition
4.3 Create a Policy
4.4 Evaluate a Policy
4.5 Fix Non-complying Policy
5 Summary

Read complete article here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Introduction to Policy Management – Enforcing Rules on SQL Server

I have previous written article about SQL SERVER Database Coding Standards and Guidelines Complete List Download. I just received question from one of the blog reader is there any way we can just prevent violation of company policy. Well Policy Management can come into handy in this scenario.

If our company policy is to create all the Stored Procedure with prefix ‘usp’ that developers should be just prevented to create Stored Procedure with any other prefix. Let us see small tutorial how to create conditions and policy which will prevent any future SP to be created with any other prefix.

Let us first create condition which defines that name of the database object should start with ‘usp’.

policy1 SQL SERVER   2008   Introduction to Policy Management   Enforcing Rules on SQL Server

policy2s SQL SERVER   2008   Introduction to Policy Management   Enforcing Rules on SQL Server

Now create policy for all the Stored Procedure of the database and assign previously created condition of naming convention to all the Stored Procedure of the database.

policy3 SQL SERVER   2008   Introduction to Policy Management   Enforcing Rules on SQL Server

Make sure that Policy is enabled.

policy4s SQL SERVER   2008   Introduction to Policy Management   Enforcing Rules on SQL Server

Now try to create Stored Procedure with incorrect name. It will not let you create it but will create error.

policy5 SQL SERVER   2008   Introduction to Policy Management   Enforcing Rules on SQL Server

If you try to create Stored Procedure prefixed with ‘usp’ it will create it successfully.

policy6 SQL SERVER   2008   Introduction to Policy Management   Enforcing Rules on SQL Server

Policy Management is very detailed subject and I will write more tutorial about this subject in future.

Reference : Pinal Dave (http://blog.SQLAuthority.com)