What is the best way to access system table "sysschedules" on RDS SQL Server instance

0

We are trying to run a specific query on a SQL Server RDS database. I believe the dba is working on setting up some scheduled jobs or tasks. His query is attempting to access the system tables:

msdb.dbo.sysjobs msdb.dbo.sysjobactivity msdb.dbo.sysjobschedules msdb.dbo.sysschedules

When we try to run the query, we are getting this error:

“The SELECT permission was denied on the object ‘sysschedules’, database ‘msdb’, schema ‘dbo’.”

We are trying to run the query as the RDS admin user. From my searching around, it seems that the sysschedules table might be one of the tables that AWS is blocking direct access to, even for the admin users. I saw some mentions of people using stored procedures to access these reserved tables, but I haven't been able to find a stored procedure to access the sysschedules table.

We really want to keep this SQL server instance managed on RDS and not have to self-manage on EC2, but we also need access to this table. Any ideas on how to access this reserved table are appreciated.

已提問 6 個月前檢視次數 1387 次
1 個回答
0

Hi,

Unfortunately, 'sysschedules' is a managed table by RDS and you would not be able to query it directly. You can run the below commands to find what level of access is allowed on a particular table/s especially while using system databases.

Use MSDB 
 Go 
 EXEC sp_table_privileges @table_name ='SYS%' ; 



If your region supports it, you could consider utilizing RDS Custom SQL Server [1] [2] or the EC2 route.

References: [1] RDS Custom: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-setup-sqlserver.html [2] Regions: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.RDS_Fea_Regions_DB-eng.Feature.RDSCustom.html#Concepts.RDS_Fea_Regions_DB-eng.Feature.RDSCustom.sq

AWS
支援工程師
Kyle_B
已回答 5 個月前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南