时间戳

首页 > 解决方案 > 我在运行查询时启动了 RDS SQL Server（SQL Server Web 版）并且它抛出了权限被拒绝错误

sql-server - 我在运行查询时启动了 RDS SQL Server（SQL Server Web 版）并且它抛出了权限被拒绝错误

问题描述

我在查询下方运行，错误是

对象“sysschedules”、数据库“msdb”、模式“dbo”的 SELECT 权限被拒绝。

SQL Server Web 版
引擎版本
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];

标签： sql-serveramazon-web-servicesamazon-rdssql-server-ce

解决方案

不幸的是，到目前为止，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];

推荐阅读