ExecuteSql()

One of the key development tenets of Policy-Based Management was to raise the level of abstraction of management. The analogy I've used time and time again is Security Zones in Internet Explorer. Prior to Security Zones we had to scroll through pages and pages of configuration options. Security Zones greatly simplifies this task by providing ready made templates for the settings. However, every so often it's necessary to by pass the security zones and configure the properties directly or to configure a property that's not covered by the Security Zones. We recognized that in a v1 release we wouldn't be able to cover the entire system with facets (necessary to raise the level of management abstraction) so we created a so called backdoor. The backdoor allows the policy author to create a condition expressed in Transact-SQL. Effectively this means if you can write a query to check for the desired state you can wrap that query in a policy. For security purposes the policy can only be automated to check for compliance on a schedule. I'll double back to security in a moment. First let's look at the basics of the ExecuteSql() function.

Here's the function signature:

Variant ExecuteSql(string returnType, string sqlQuery)

Here are the arguments:

returnType - Specifies the return type of data that is returned by SQL Server. The valid literals for returnType are Numeric, String, Bool, DateTime, Array, and GUID.

sqlQuery - String that represents the query to be run.

Note: Be sure to wrap both the returnType and sqlQuery in single quotes. If the sqlQuery contains quotes you'll need to escape them by doubling up the single quotes.

The return value:

Return value - The first column in the first row of the result set returned by the Transact-SQL query.

The ExecuteSql() function can be used against any facet and it'll be executed once for each target in the target set. For example, if it's used on the server facet it'll only execute once. If it's used against the database facet it'll run once for each database. When run against databases or objects within a database, the context for the execution is the current database.

For added flexibility and power the context of schema bound objects is passed into the SQL statement using ADO.NET parameter substitution.

@@ObjectName - corresponds to the name field in sys.objects. The variable will be replaced with the name of the current object.

@@SchemaName - corresponds to the name field in sys.schemas. The variable will be replaced with the schema for the current object if the current object belongs to a schema.

Security

The sqlQuery can contain any valid DDL or DML Transact-SQL statement. This includes SELECT, DELETE, UPDATE, TRUNCATE, CREATE, GRANT, etc. Because of this the initial implementation prevented policies using ExecuteSql() from being automated. They could only be manually run and they would run in the context of the current user. It is the responsibility of the user to ensure the policy isn't doing something malicious. In other words the user should treat the policy just like they'd treat any file containing Transact-SQL - don't blindly execute it!

We subsequently relaxed this restriction. We recognized how powerful this capability is and the early user feedback indicated a strong desire to automate policies containing SQL. To allow this we had to address the security concerns. A user only needs to be a Policy Administrator in MSDB to author policies, a very low privileged role. Due to the security concerns we considered eliminating this role and forcing all policy administration to be performed by an SA. There are times I think we should have done this but instead we opted to keep the Policy Administrator role and instead run the Transact-SQL under a proxy account. The proxy account only comes into play when the policy is run on a schedule. When it's run on demand it executes under the current user's context.

The proxy account is called ##MS_PolicyTsqlExecutionLogin##. You'll find this login under the "Logins" folder in Management Studio and master.sys.syslogins. By default the login has very limited permissions. You will likely need to extend the privileges of this user to accommodate the SQL you need to run under policy.

Note: Every permission you grant to ##MS_PolicyTsqlExecutionLogin## you are effectively granting that permission to all users in the Policy Administrator role.

Let's walk through an example. Someone recently came to me asking how to create a policy that checks that all tables have data compression enabled. They didn't care if it's row or page level compression, they simply want to check that compression is on.

Compression is a property of a partition. Tables can have one or more partitions. This means for each table we need to make sure that all partitions are compressed. Unfortunately there is no facet over partitions. This was simply a scoping decision for SQL2K8. There is no technical reason we couldn't do it. But this means the only way to author this policy is to write Transact-SQL. It's always best to state the policy in plain English (or in your native language) "All tables must have all partitions compressed." Since we're dealing with table I'll author the policy/condition on the Table facet. We could do this at the database level but I want to flag individual tables that have uncompressed partitions rather than flagging databases with tables with uncompressed partitions.

I'll start by constructing the raw Transact-SQL that returns schema name, table name and partition count where the partition isn't compressed. In an ideal situation this query would not return any rows. This query can be run in your favorite query tool.

SELECT sch.name, tab.name, COUNT(*)
FROM sys.partitions part
JOIN sys.tables tab
ON (part.object_id = tab.object_id)
JOIN sys.schemas sch
ON (tab.schema_id = sch.schema_id)
WHERE part.data_compression = 0
GROUP BY sch.name, tab.name

There are a few modifications we'll need to make to the SQL before we wrap it in a condition. First we need to set it up to accept the context of a specific table. Remember, we'll run the SQL for each table. To do this we need to add to the WHERE clause and use our special parameters.

SELECT sch.name, tab.name, COUNT(*)
FROM sys.partitions part
JOIN sys.tables tab
ON (part.object_id = tab.object_id)
JOIN sys.schemas sch
ON (tab.schema_id = sch.schema_id)
WHERE sch.name = @@SchemaName
AND tab.name = @@ObjectName
AND part.data_compression = 0
GROUP BY sch.name, tab.name

This gets us almost all the way there but remember, the return value is only the first value in the first row of the result set. This means we need to only return COUNT(*) and while we're at it we need to remove the GROUP BY.

SELECT COUNT(*)
FROM sys.partitions part
JOIN sys.tables tab
ON (part.object_id = tab.object_id)
JOIN sys.schemas sch
ON (tab.schema_id = sch.schema_id)
WHERE sch.name = @@SchemaName
AND tab.name = @@ObjectName
AND part.data_compression = 0

Excellent, the final step is to construct the full function. The return value is numeric and we don't have any strings to escape so this is a pretty straight forward step.

ExecuteSql('Numeric', 'select COUNT(*)
FROM sys.partitions part
JOIN sys.tables tab
ON (part.object_id = tab.object_id)
JOIN sys.schemas sch
ON (tab.schema_id = sch.schema_id)
WHERE tab.name = @@ObjectName
AND sch.name = @@SchemaName
AND part.data_compression = 0')

The SQL is constructed so that it returns 0 if all of the current table's partitions are compressed and some value greater than 0 if at least one partition is not compressed. Since conditions are constructed in the positive we'll compare the result to 0. In other words if it equals 0 the table is considered to pass the policy else it's considered to violate the policy.

Here are screen shots of the condition I created and the advanced condition editor.

Here's a screen shot of the evaluation results for the policy when run against one of my test databases which has two tables, one has all partitions compressed and one has none.

ExecuteSql() is a powerful function that allows you to create some very powerful policies. Given the security considerations you'll want to use it wisely and choose carefully who you allow to create policies.

Have you created a cool policy using ExecuteSql()? If so you should post it up on https://www.codeplex.com and share it with others. Or you can reply to this posting with the details.

_____________

About the Author:
Dan Jones is a PM on the SQL Server Manageability team at Microsoft.