SQL Server Management Studio v19.3 fails to delete job in RDS for SQL Server instance

Question

When trying to delete a job using SSMS v19.3 SQL Server Agent UI getting error: The EXECUTE permission was denied on the object 'xp_regread', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229) According to this RDS documentation only this command needs to be run in order to delete the job successfully: EXEC msdb..sp_delete_job @job_name = 'my job'; SSMS needs to identify it's an RDS instance and only run it.

Answer 1

The link you post explicitly says;

You can't use SSMS to delete SQL Server Agent jobs. If you try to do so, you get an error message similar to the following:> The EXECUTE permission was denied on the object 'xp_regread', database 'mssqlsystemresource', schema 'sys'.

That is, you need to run sp_delete_job as described in the RDS documentation.

SSMS needs to identify it's an RDS instance and only run it.

Or are you asking for a feature request to SSMS? You can submit those here: https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0

Answer 2

I spoke to product group members about this.
The problem (unfortunately) is at the RDS end, not the SSMS end.
sp_delete_job only requires the ability to use xp_regread for MSX related jobs. These are jobs where a value is passed for the parameter @originating_server. For "normal" jobs, that parameter should be blank. For some reason, when you execute that at the RDS end, SSMS thinks it's an MSX job and so it fills in that parameter for you. That causes it to use the code path that uses xp_regread, and that requires sysadmin which you don't have on RDS.
That's why they've documented that you have to run sp_delete_job manually, and not from SSMS. When you do that, you don't pass a parameter for @originating_server, the code path with xp_regread isn't needed, and it then works.
It's a pity that it doesn't work but they have documented it as a known issue, and provided a workaround.

Answer 3

Hi @Yoni Sade, As the docs said, the error should occur, what's your concern? Regards， Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.