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

Yoni Sade 1 Reputation point
2024-02-12T11:11:58.6633333+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,231 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 98,751 Reputation points
    2024-02-12T22:37:07.9533333+00:00

    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

    1 person found this answer helpful.

  2. Greg Low 355 Reputation points Microsoft Regional Director
    2024-02-15T03:07:33.9033333+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  3. ZoeHui-MSFT 30,411 Reputation points
    2024-02-15T02:40:43.2733333+00:00

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


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

    0 comments No comments