AWS RDS SQL Server Agent Tables

0

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?

1 Answer
0

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.

ArianaH
answered 2 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