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

Question

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.

Answer

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

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

Conteúdo relevante