AWS RDS MSSQL Standard Edition - SqlServer Agent Tree not visible

0

Hi, I have an AWS RDS MSSQL standard edition, I'm connected as the master user. The SQLServer Agent Tree is not displaying via SSMS client. Weirdly if I use Azure Data Studio the agent jobs are visible / usable, and I am using the same master user.

Any advice on why this is not visible using SSMS compared to Azure Data Studio. Thanks

fran
asked 4 months ago1415 views
3 Answers
0

Hello AWS Customer,

SQL Server Agent is supported in Amazon Relational Database Service (Amazon RDS), however, with restricted permissions due to the security guardrails that a managed Database Platform provides.

The role required to view jobs and their properties is the ‘SQLAgentUserRole’. By default this role is added to the master RDS user, since you state however that you can’t list jobs while using master user, I recommend reviewing permissions associated with this user and reassigning the SQLAgentUserRole should it be needed. You can do this by following the guidance outlined in the below doc:

[+] https://aws.amazon.com/blogs/database/leveraging-sqlagentoperatorrole-in-rds-sql-server/#:~:text=View%20SQL%20Server%20Agent%20on%20SQL%20Server%20Management%20Studio%20(SSMS)

You must enable ‘SQLAgentUserRole’ and remove SQLAgentOperatorRole for master user to have visibility on SQL Agent Job. This method will allow only the user who created the job to be able to see the job. Other users who need to be able to see the job are not going to have visibility. As other users may need access to the job, it may become an issue. To resolve this, please run the following script:

USE msdb GO ALTER ROLE SQLAgentUserRole ADD MEMBER UserName GO GRANT ALTER ON ROLE::[SQLAgentOperatorRole] to UserName GO ALTER ROLE SQLAgentOperatorRole ADD MEMBER UserName GO

After each use and before logging off, the user must be removed from SQLAgentOperatorRole. To do this, use this script:

USE msdb GO ALTER ROLE SQLAgentOperatorRole DROP MEMBER UserName GO

[+] Leveraging SQLAgentOperatorRole in RDS SQL Server - https://aws.amazon.com/blogs/database/leveraging-sqlagentoperatorrole-in-rds-sql-server/

Repeat the previous steps in order to give other users access to jobs.

answered 4 months ago
0

perfect ty, once I removed SQLAgentOperatorRole from this login I can now see the sql Agent Tree. Many thanks for your time

fran
answered 4 months ago
0

This resolves my issue

fran
answered 4 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