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.

1 Answer
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
SUPPORT ENGINEER
Kyle_B
answered 3 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions