当我运行查询时，我启动了RDS SQL Server (SQL Server Web Edition)，它抛出了perm

Question

我运行下面的查询，错误是

对象'sysschedules'，数据库'msdb'，模式'dbo'的SELECT权限被拒绝

SQL Server Web Edition
引擎版本
15.00.4073.23.v1

如何解决这个问题?

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

不幸的是，到目前为止，RDS还不支持这一点。看起来RDS不允许您查询这些系统表:

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

对应的AWS论坛线程:https://forums.aws.amazon.com/thread.jspa?threadID=220807

你需要从你的查询中删除对这些表的连接，这给了你以下-这是可能对SQL Server实例的RDS执行的:

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];