By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

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?

Topics
Migration & ModernizationDatabaseAnalytics
Tags
Migration & ModernizationMicrosoft SQL ServerAmazon Relational Database Service
Language
English
ArianaH
asked 2 years ago419 views
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 years 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

Relevant content