I have created a SQL condition using ExecuteSql() and enabled a policy based on this condition for Stored Procedure facets. When I evaluate this policy “On demand”, it runs perfectly fine and gives the desired pass/fail results. However, if I run this “On schedule”, the policy runs incorrectly and from the SQL Profiler, I am able to figure out that there is an error thrown – “Must declare the scalar variable @@ObjectName”. Also, for testing purposes, I have made “##MS_PolicyTsqlExecutionLogin##” user as db_owner on the databases I am working with. So it shouldn’t be a permissions issue probably.
The query I have specified in the condition is,
ExecuteSql('Numeric', 'Select count(*) From sys.database_permissions Permission Join sys.objects Sysobject ON Permission.major_id = Sysobject.object_id Join sys.database_principals Principal ON Permission.grantee_principal_id=Principal.principal_id Where (Permission.state = ''G'' or Permission.state = ''W'') AND Sysobject.object_id in ( select object_id from sys.objects where name = @@ObjectName ) ')
I am also unable to get Enterprise Policy Management framework to work in order to automate the execution of these policies. Any ideas on what I might be doing wrong or should be doing differently?