I have recently been looking at Policy Based Management with a view to using it to rapidly review servers in our domain to ensure they meet our configuration requirements. One of our tests is that every server login must only have a default database that meets the following criteria:

• The database must be online.
• The database must not be any system database other than TembDB

Currently this is tested with a piece of TSQL that checks the sys.server_principals table and the sys.databases table to confirm this.

Checking this with a Policy should be pretty simple I thought.

Let’s take a look. Start by opening the Management node SSMS Object Explorer

I am going to assume that you are to a certain extent familiar with the PBM theory but here is a rapid overview

• Facets relate to areas of SQL Server and have properties that relate to a specific value of a setting in SQL Server( e.g. the Login Facet has properties that include IsDisabled, IsLocked, CreateDate, DefaultDatabase, Name and so on)
• Conditions are rules and tests that get applied to multiple Facet properties
• Policies describe how and when Conditions are applied to servers/server objects

So, for the checks I want to make we will clearly be making a Condition that tests the Login Facet and the DefaultDatabase property. Let’s make a new Condition

Type in a name and choose the Login Facet. While you are working in this UI keep an eye on the message box at the top of the window, it isn’t too obvious but this contains all the information you are going to get about debugging and errors that crop up.

Next move, select the Facet @DefaultDatabase property in the Field column and see what Operator options are available

This looks useful as it seems to show us that we can test the @DefaultDatabase value of every Login for it’s inclusion in a list of databases. In most cases a value can be typed into the Value column for the Field to be matched against (e.g. @IsDisabled = False) sadly this isn’t going to be that simple. Despite having all the functions and expressions to cope with the test that we want to carry out (i.e. we query sys.databases for a list of online user databases) it is not possible to build the condition this way. The Value has to be a single scalar value to test against.

To achieve this we need to get creative with the use of the ExecuteSQL function in combination with the Concatenate() function and the @DefaultDatabase value. Firstly, choose the = operator and click the ellipsis so that we can build a more complex expression.

Here is the function that you need to enter (note I am also testing that no one has their default database set to any report server database.

ExecuteSql(‘String’, Concatenate(Concatenate(‘Select d.name from sys.databases as d WHERE d.name = ”’, @DefaultDatabase),”’ and d.state_desc = ”online” and d.database_id > 4 and d.name not like ”ReportServer%”’))

If you don’t get it quite right, this is how you advised by the Condition editor evaluating the function

Let’s review this expression and understand what is happening and how it is working.

The expression builder provides access to Facet Property values and a limited set of Functions to allow string manipulation, comparison and calculation. We need to use a combination of static SQL values with a property value and the Concatenate function to build a valid SQL string that we can use in the ExecuteSql function.

The concatenate function only accepts 2 strings so the inner one here concatenates the beginning of out SQL statement and the value of the default database of the Login being checked for matching the policy. The value returned by this function is itself a parameter of the outer Concatenate function with adds the remaining part of the SQL statement that we want to use. This whole string is then passed into the ExecuteSQL function. We know that our statement will either bring back the name of the default database or nothing from sys.databases. If it brings back the default database name then we will have a value in the condition that evaluates as true and it it brings back nothing then the condition will be False and our Policy will alert us that we need to review the Login settings. Click OK to save the Policy when you are finished.

So, to put this Condition to use we need to create a policy that will evaluate it. Right click the Policies node and select New Policy.

Select our newly created Condition and click OK to complete the step.

Once created a Policy can be evaluated by right clicking and choosing Evaluate. If the Condition that is being evaluated contains any ExecuteSql function/s then SSMS will prompt you to confirm you trust the code and want to execute it as the SQL. This is because it is not parsed for any possible damage it could do (INSERT, UPDATE and DELETE statements can be executed here leading to potentially disastrous consequences).

Once the evaluation has run you will get a result set that should show which Logins are configured correctly and those that could experience problems due to their default database being offline.

Hopefully this has shown you how to compose a SQL statement that can be successfully evaluated by SQL Server Policies so that you can quickly asses your servers for conditions that require a little help from SQL statements and how to have them return a scalar value that is ready to be evaluated by the Policy Condition.

Many thanks to Jon Gurgul @jongurgul MCM: SQL 2008. MCSM: Data Platform for his input in getting this complete.