AWS Database Blog

Leveraging SQLAgentOperatorRole in RDS SQL Server

This blog post was reviewed and updated May 2022, to improve readability by adding more step-by-step guidance.

SQL Server is a relational database management system developed by Microsoft. Amazon RDS for SQL Server makes it easier to setup, operate, and scale SQL Server deployments in the Cloud. One of the RDS SQL Server components is SQL Server Agent. The SQL Server Agent is a Microsoft Windows service that runs scheduled administrative tasks, which are called jobs in SQL Server.

DBAs and users have long used SQL Server Agent to schedule jobs on a SQL Server instance. SQL Server Agent has always been supported in Amazon Relational Database Service (Amazon RDS), however, with restricted permissions due to the security guardrails that a managed Database Platform provides.

This article helps customers to better leverage SQL Server Agent in an Amazon RDS for SQL Server environment to view the following items.

  • List of Agent Job(s)
  • Properties of Agent Job(s)
  • Execution information of Agent Job(s)
  • Historical execution information of Agent Job(s)

New SQL Server Agent Permissions

RDS SQL Server supports SQL Server Agent on the Enterprise, Standard, and Web editions. The master user of the RDS SQL Server instance is added to the SQLAgentUserRole by default. The master user can also add other users to the SQLAgentUserRole and any user that is part of this role, can create SQL Agent jobs. Only the user that created a job can view, enable, disable, edit, execute, start, and stop the job. While this is sufficient for some use cases, there are other use cases where this level of access is insufficient. For example, in a team of Database Administrators (DBAs), where maintenance jobs are created using individual user accounts, one DBA might start a job when the DBA who created the job is unavailable. To address this and other such requirements, starting February 25, 2019, RDS SQL Server added support for the SQLAgentOperatorRole. Due to an underlying SQL Server implementation, the SQLAgentOperatorRole cannot be handled the same way as the SQLAgentUserRole in RDS. To improve readability, going forward, the SQLAgentUserRole is referred to as the user role and the SQLAgentOperatorRole as the operator role.

View SQL Server Agent on SQL Server Management Studio (SSMS)

To visualize the SQL Server Agent tree in the SQL Server Management Studio, there are few steps that the user needs to follow, this will allow the users to properly see the jobs.

Adding the user on the SQLAgentUserRole & Granting Alter Role on SQLAgentOperatorRole

Using the RDS master credentials, login into the RDS SQL Server Instance and grant to the desired user the SQLAgentUserRole.

USE msdb
GO
IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = 'UserName')
BEGIN
CREATE USER UserName FROM LOGIN UserName
END
GO
ALTER ROLE SQLAgentUserRole ADD MEMBER UserName
GO
GRANT ALTER ON ROLE::[SQLAgentOperatorRole] to UserName
GO

The above script will perform the following actions on the RDS SQL Server Instance.

  • Creates the user on msdb database, in case it doesn’t exists.
  • Adds the user on the SQLAgentUserRole, so the SQL Server Agent tree on SSMS can be seen
  • Grants alter permissions on the SQLAgentOperatorRole to the user. This allows the user to add/remove itself from that role.

Once you login to the instance with the user added to the SQLAgentUserRole, this should be your normal user view.

As part of the role SQLAgentUserRole, the user is able to see the SQL Server Agent tree, the Jobs tab, as well as the Job Activity Monitor. Notice you will not see any jobs under the Jobs tab, even though they may already exist.

This is because you have not added yourself yet to the role SQLAgentOperatorRole, and it is an important step to see the jobs. 

Adding itself to SQLAgentOperatorRole

To add yourself to the above-mentioned role, connect to the RDS SQL Server Instance, with the user that needs to see the jobs, and run the following script.

use msdb
go
ALTER ROLE SQLAgentOperatorRole ADD MEMBER UserName
GO

After the below script is run, right click on the Jobs folder, and choose Refresh as shown below.

When you perform this action, you will notice the Jobs tab will have a + (plus) button, click to expand, and you should be able to view the list of SQL Server Agent Jobs.

View Agent Job Information

To view the Agent Jobs history, double-click on the Job Activity Monitor, and that should give you the ability to view the Job execution history.

Remember that only the user who initially created the SQL Agent Job, will have permissions to edit the properties of the job. You can see below, that the options for changing the job are all greyed out.

Removing itself from SQLAgentOperatorRole

Before you disconnect from the RDS SQL Server Instance, you will need to remove yourself from the SQLAgentOperatorRole.

To do that, simply, run the following query before disconnecting or closing the Management Studio.

USE msdb
GO
ALTER ROLE SQLAgentOperatorRole DROP MEMBER UserName
GO

After that, if you click to refresh the Jobs tab, the list of Jobs will disappear.

Forgetting to remove yourself from SQLAgentOperatorRole

In case you forget to remove yourself from SQLAgentOperatorRole, the next time you login in the SQL Server Instance, you will receive the following error message in the screen.

The internal SQL Server login implementation for a user who is part of the operator role involves calls to a few internal stored procedures. One of the procedures that is invoked, is the extended stored procedure – xp_regread. Access to this extended stored procedure is blocked in RDS and so a connection to an RDS SQL Server instance as a user who is part of the operator role, can fail with a login error. From our testing, login errors are seen only in SSMS. Logins from Azure Data Studio, SQL Developer, and Toad do not have a problem.

If you click on the OK button on the SSMS error message, a session to SQL Server is created, but the SQL Server Agent drop-down menu in SSMS does not show up. All SQL Server Agent T-SQL commands that are expected to work for a user who is part of the operator role will continue to do so. If SQL Server Agent is administered programmatically or via T-SQL commands, the SSMS error can be safely ignored. If using the SSMS graphical user interface is important, you need follow the below steps.

  1. Choose OK.
  2. Run the script provided on Removing yourself from SQLAgentOperatorRole section of this document.
  3. Right Click on the RDS SQL Server Instance, and choose Refresh.
  4. Then SQL Server Agent tree is shown again.
  5. Run the script provided on Adding itself to SQLAgentOperatorRole section of this document.
  6. Right click on the Jobs tab, and choose the Refresh button.

The list of jobs will appear again.

Summary

To summarize, the SQLAgentOperatorRole is now fully supported in RDS and any command-based interface to SQL Server Agent works seamlessly. Some users who are using the graphical user interface in SSMS, can see a login error. However, as explained above there are ways to work around this and it does not indicate an underlying error in the SQL Server instance. Customers who do not want to use a shared administrative account, can now create SQL Server Agent jobs under individual accounts, knowing that they can leverage the operator role to control those jobs.

If you have any questions or comments about this blog, please use the comments section below.

About the Authors

Prashant Bondada is a Senior Database Engineer at Amazon Web Services. He works on the RDS team, focusing on commercial database engines, SQL Server and Oracle.

Marcos Freccia is a Sr. Consultant on Database Migrations with the AWS Professional Services Team. He has been supporting and enabling customers on their journey to migrate and modernize their database solutions from on-premises Data Centers to AWS.