Policy-Based Management(Check The Trustworthy Option Of Databases-Database Facet)

Policy-Based Management is a feature that introduced with SQL Server 2008. It allows us to set the rules we want in our systems, to determine our standards and to perform our controls automatically. For example, we can create a policy for control purposes so that the names of our stored procedures do not start with a number or database recovery models are not simple.

Before reading this article, I recommend reading the article “How To Give Restrict Access To dmw and dmf To Logins Without Giving View_Server_State Permission” to understand the concept of Trustworthy. You can use the search section of our site for more detailed information.

To comprehend “Policy Based Management (PBM)”, you need to understand some concepts.

Facet: A feature that can be managed by the PBM. For example, there is a facet named database. And by using this facet, you can create a policy that controls the trustworthy option of the database.

You can access all facets via SSMS as follows. You have to double-click on Facet details.

Condition: checks whether the sub properties of the related facets meet the specified condition.

Let’s create a policy that checks the databases that have Trustworthy option enabled.

We click New Policy on the Management> Policy Management> Policies tab as follows

We give a name to Policy and we need to create a condition from Check Condition.

We click on New Condition.

We give a name to the condition from the Name section.

We choose the Database facet in the Facet section.

By clicking “…” in the Expression section, we select @Trustworthy , which is the sub property of the Database facet.

We select “=” in Operator and “False” in Value.

Thus, if there is a database that has Trustworthy property open, the policy will fail.

By selecting “On Schedule” from the Evaluation Mode section, we determine at what intervals the policy will be checked, and then click the Enable check box.

If we choose On Demand, we only do checks when we execute policy.

In the server restriction section, if you have a server-based condition, you can control your condition by creating a server-based condition.

If we want to automatically check at regular intervals, we click new in schedule and determine the frequency of the policy check. As you can see below, we set it to work once every day at 00:00:000.

We created our policy. To run manual, we click Evaluate as follows.

When I performed this operation in my local, I got a result like this. As you can see, because the trustworthy property of the two databases is open, policy has failed.

Click on View to get more detailed information.

Because the policy is failing, when you refresh on Instance, a red x sign appears next to Instance. The reason for this is that there is an unsuccessful policy that SQL Server wants to notify us. When you see this mark, you must look at the defined policies and correct the problems.

There are many controls you can do on SQL Server with Policy Based Management. And I think a professional database administrator should use this feature that SQL Server provide us with all the details. You can see other controls you can do with Policy Based Management by typing “Policy Based Management” in our search section.