SELECT denied despite sysadmin privileges (policy management SQL 2008)

Question

The initial situation:

I'm using Microsoft SQL Server 2008 (SP4) - 10.0.6241.0 (X64),Enterprise Edition (64-bit) on a virtual machine (clustered instance).

Under Policy Management ...

... I defined the following condition:

ExecuteSql('Numeric', '
USE [msdb];
SELECT [enabled]
FROM [dbo].[sysjobs]
WHERE [name] = ''updSTAT Agentur- und Bildempfangsdatum'';
')

The policy using this condition is active, evaluation mode is set to "On schedule".

Finally I set up an alert which sends a message to an operator, if error 34052 is raised.

So far so good!

If I evaluate the policy manually, everything is working fine. If the policy is evaluated on schedule the following error is raised (error 229, severity 14 in policy history, not in the job agent's history):

The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

The job which has been generated automatically has 2 steps. The 1st step named "Verify that automation is enabled." has type Transact-SQL-Script:

IF (msdb.dbo.fn_syspolicy_is_automation_enabled() != 1)
    BEGIN
        RAISERROR(34022, 16, 1)
    END

The field Run As is empty. As far as I know the SQL Server Agent Service Account is used. SQL Server Agent is running with an AD user account having sysadmin privileges.

On the other hand I cannot assign a proxy to subsystem T-SQL.

The second step named "Evaluate policies." has type PowerShell and is executed as SQL Server Agent Service Account.

What the heck is the problem here? What have I missed?

Thanks a lot in advance!

Yes, the job is owned by sa. I've added the information above. — D.C., Oct 14 '16 at 13:14
Possible dupe with this question: http://stackoverflow.com/questions/6370881/the-select-permission-was-denied-on-the-object-sysjobs-database-msdb-schem — smoore4, Oct 14 '16 at 13:43
GRANT SELECT ON [dbo].[sysjobs] TO [AgentAcc] doesn't solve the problem. By the way the account has sysadmin privs. So it should be able to realize a simple Select. Yes, GRANT ... TO public solved the problem. But I don't want grant Select to public(!). — D.C., Oct 14 '16 at 14:07
Did you try to trace the schedule execution and see under which specific credentials it is attempting to perform the dreaded `select`? — ajeh, Oct 14 '16 at 14:09
SQL Server Profiler confirmed that SQL Server Agent Service creds are used. — D.C., Oct 14 '16 at 14:30

SELECT denied despite sysadmin privileges (policy management SQL 2008)

0 Answers0