- You create a policy by using Policy-Based Management (PBM) in Microsoft SQL Server 2008, Microsoft SQL Server 2008 R2, or Microsoft SQL Server 2012.
- The evaluation mode for the policy is On Schedule.
- One of the Conditions for the policy contains the ExecuteSql() function.
In this scenario, when the SQL Server Agent job is executed, the agent generates false alerts, and the following error message is logged in the SQL Server error log file:
Policy '<Policy name>' has been violated.
Note This issue does not occur when you run this job manually.
The issue is caused by the usage of the ExecuteSql() function within the policy. This function allows the policy author to create a condition expressed in Transact-SQL and can also execute any Transact-SQL code within PBM. Therefore, by default, the security context that the code runs under is a very low-privileged account ##MS_PolicyTsqlExecutionLogin##. The account ##MS_PolicyTsqlExecutionLogin## is not given any permissions for any database in addition to the msdb database. However, when a scheduled job runs, one of the first statements that is automatically added is the use [<DBName>] statement. This statement causes the policy check to fail.
When you run this job manually, SQL Server uses your current security context. As long as you have permission to run the queries in the policy, this job will evaluate correctly.
For example, the public role is good enough for certain queries to execute. This role can be changed as necessary based on business needs and company policies. However, it is very unlikely to change, as this behavior is intended for security reasons.
Article ID: 2923956 - Last Review: 4 Feb 2014 - Revision: 1