AWS RDS SQL Server Agent Tables

Question

Hello, I'm trying to migrate an SQL Server database to RDS which uses the SQL Server agent and has a store procedure that selects from the SQL Server Agent Table dbo.sysjobsteps as per https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobsteps-transact-sql?view=sql-server-ver15. 
I've tried assigning permissions as per https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.Agent.html but I keep getting the error: The SELECT permission was denied on the object 'sysjobsteps', database 'msdb', schema 'dbo'. (even with the master user).
On the other hand, other tables like dbo.sysjobs do return the expected results.

Is this expected and does anyone know a workaround?

Answer

If anyone encounters the same issue, I've changed the SELECT calls to the tables to use instead the available store procedures which any user with the SQLAgentUserRole is allowed to execute.
For example, instead of selecting rows from `dbo.sysjobsteps` table, calling the `msdb.dbo.sp_help_jobstep` store procedure returns the necessary information.

AWS RDS SQL Server Agent Tables

相关内容