Ok, I have an update and a good idea what the issue is. Since our base issue is users that are part of the SQLAgentOperatorRole are unable to view/edit SQL Agent jobs (as the original error or a form of it is generated), I created the following script to test this out on several servers (7 in total). 3 of the servers generate the error, 4 did not.
SET NOCOUNT ON;
GO
/* Fully remove the test server principal if it exists */
IF EXISTS (SELECT TOP 1 1 FROM [sys].[server_principals] WHERE name = 'SQLAgentTest')
BEGIN
USE [master]; DROP LOGIN [SQLAgentTest];
END
/* Fully remove the test database principal if it exists */
IF EXISTS (SELECT TOP 1 1 FROM [msdb].[sys].[database_principals] WHERE name = 'SQLAgentTest')
BEGIN
USE [msdb]; DROP USER [SQLAgentTest];
END
GO
/* Create base account with base permissions */
USE [master]; CREATE LOGIN [SQLAgentTest] WITH PASSWORD = 'someRandomValue', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
USE [msdb]; CREATE USER [SQLAgentTest] FOR LOGIN [SQLAgentTest];
USE [msdb]; EXEC sp_addrolemember @rolename='SQLAgentOperatorRole', @membername='SQLAgentTest';
GO
/* Now test and see if the error is raised */
USE [msdb]; EXECUTE AS LOGIN='SQLAgentTest';
GO
USE [msdb]; EXEC [msdb].[dbo].[sp_help_job];
GO
USE [msdb]; REVERT;
GO
/* Remove the principals since testing was completed */
USE [master]; DROP LOGIN [SQLAgentTest];
USE [msdb]; DROP USER [SQLAgentTest];
GO
The error that is generated is close to something like
Msg 229, Level 14, State 5, Procedure xp_sqlagent_enum_jobs, Line 1
The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.
The exact error depends on the action the user is performing and the only thing that changes is the object name in the error.
Now, I'm comparing the ##MS_AgentSigningCertificate## certificate in master and msdb using this query:
SELECT 'master', *
FROM master.sys.certificates
WHERE name = '##MS_AgentSigningCertificate##'
UNION
SELECT 'msdb', *
FROM msdb.sys.certificates
WHERE name = '##MS_AgentSigningCertificate##'
ORDER BY 1;
On the 4 servers that generate an error, these certificates are not the same. On the 3 servers that do work, these certificates are the same. I'm guessing SQL Server uses this internally to apply the necessary permissions. My question is, how do I fix these certificates? Thoughts?