Hi Can you help me please? I have a problem with the certificates on out new Sql Server 2014 box. We are upgrading from Sql Server 2005...
Background: We need users to check if an agent job is running and if not start it up (need to use master.dbo.xp_sqlagent_enum_jobs; msdb.dbo.sysjobs; *msdb.dbo.sp_start_job)
*It never gets to run msdb.dbo.sp_start_job as the try catch ends sproc
Steps
• Certificate created from scratch (on calling database)
• Backup certificate
• Create Certificate on master from backup file
• Certificate login created from cert
• Certificate login given sysadmin
• Security group given execute permissions to stored procedure
• Stored procedure signed with certificate
• Execute as user (The user is a member of the windows security assigned execute permissions of sproc)
• When the procedure runs the following is outputted:
Msg 229, Level 14, State 5, Procedure xp_sqlagent_enum_jobs, Line 84
The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure spRunSSISImportProcess, Line 199
The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.
We have tried multiple certs and 2 databases one of which was created from scratch the other was a restored DB from a 2005 SQL box on a 2014 SQL box with the same output.
Can anyone help please? I'm really stuck with this one.
Thanking you for any comments/suggestions,
J.