I launched RDS SQL Server (SQL Server Web Edition) when I ran the query and It is throwing permission denied error

Question

I ran below query and error is

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

SQL Server Web Edition
Engine version
15.00.4073.23.v1

How to resolve this?

SELECT [sJOB].[job_id] AS [JobID],
       [sJOB].[name] AS [JobName],
       [sDBP].[name] AS [JobOwner],
       [sCAT].[name] AS [JobCategory],
       [sJOB].[description] AS [JobDescription],
       CASE [sJOB].[enabled] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IsEnabled],
       [sJOB].[date_created] AS [JobCreatedOn],
       [sJOB].[date_modified] AS [JobLastModifiedOn],
       [sSVR].[name] AS [OriginatingServerName],
       [sJSTP].[step_id] AS [JobStartStepNo],
       [sJSTP].[step_name] AS [JobStartStepName],
       CASE WHEN [sSCH].[schedule_uid] IS NULL THEN 'FALSE' ELSE 'TRUE' END AS [IsScheduled],
       [sSCH].[schedule_uid] AS [JobScheduleID],
       [sSCH].[name] AS [JobScheduleName],
       CASE [sJOB].[delete_level]
            WHEN 0 THEN 'Never'
            WHEN 1 THEN 'On Success'
            WHEN 2 THEN 'On Failure'
            WHEN 3 THEN 'On Completion'
       END AS [JobDeletionCriterion]
FROM [msdb].[dbo].[sysjobs] [sJOB]
     LEFT JOIN [msdb].[sys].[servers] [sSVR] ON [sJOB].[originating_server_id] = [sSVR].[server_id]
     LEFT JOIN [msdb].[dbo].[syscategories] [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]
     LEFT JOIN [msdb].[dbo].[sysjobsteps] [sJSTP] ON [sJOB].[job_id] = [sJSTP].[job_id]
                                                 AND [sJOB].[start_step_id] = [sJSTP].[step_id]
     LEFT JOIN [msdb].[sys].[syslogins] [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]
     LEFT JOIN [msdb].[dbo].[sysjobschedules] [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
     LEFT JOIN [msdb].[dbo].[sysschedules] [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [JobName];

Answer 1

Unfortunately as of now, this is not supported in RDS. It looks like RDS doesn't give you access to query these system tables:

• [msdb].[dbo].[sysjobsteps]
• [msdb].[dbo].[sysjobschedules]
• [msdb].[dbo].[sysschedules]

Corresponding AWS forum thread: https://forums.aws.amazon.com/thread.jspa?threadID=220807

You need to remove joins to these tables from your query, which gives you following - which is possible to execute against RDS for SQL Server instance:

SELECT [sJOB].[job_id] AS [JobID],
       [sJOB].[name] AS [JobName],
       [sDBP].[name] AS [JobOwner],
       [sCAT].[name] AS [JobCategory],
       [sJOB].[description] AS [JobDescription],
       CASE [sJOB].[enabled] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IsEnabled],
       [sJOB].[date_created] AS [JobCreatedOn],
       [sJOB].[date_modified] AS [JobLastModifiedOn],
       [sSVR].[name] AS [OriginatingServerName],
       CASE [sJOB].[delete_level]
            WHEN 0 THEN 'Never'
            WHEN 1 THEN 'On Success'
            WHEN 2 THEN 'On Failure'
            WHEN 3 THEN 'On Completion'
       END AS [JobDeletionCriterion]
FROM [msdb].[dbo].[sysjobs] [sJOB]
     LEFT JOIN [msdb].[sys].[servers] [sSVR] ON [sJOB].[originating_server_id] = [sSVR].[server_id]
     LEFT JOIN [msdb].[dbo].[syscategories] [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]
     LEFT JOIN [msdb].[sys].[syslogins] [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]
ORDER BY [JobName];