AWS Database Blog

Leveraging SQLAgentOperatorRole in RDS SQL Server

SQL Server DBAs and users have long used the SQL Server Agent to schedule jobs on a SQL Server instance. It’s a handy tool that can invoke jobs on a schedule, on an ad hoc basis or in response to an event. SQL Server Agent has always been supported in Amazon Relational Database Service (Amazon RDS), however, with restricted permissions. For example, even the RDS master user could not use the SQL Server Management Studio (SSMS) user interface, to see all the jobs scheduled on an instance. With a recent change in RDS, more permissions have been granted to the master user, and the above-mentioned limitation no longer exists. This blog post explains the changes to the permissions along with a way to consume the new permissions seamlessly.

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.

The Operator Role

Users who are part of the operator role can enable, disable, view, execute, start, and stop jobs created by other users. They can edit only jobs that they created. Please refer to Microsoft documentation for a full list of allowed actions. The operator role contains all privileges of the user role. So, users who are part of both the roles have all privileges associated with the operator role. In RDS, unlike the user role, to which the master user is added by default, the master user is not added to the operator role – why this is done in RDS is explained further down in the blog. However, privileges have been granted to the master user to add themselves or any other user to the role.

Getting Access To The Operator Role

The master user can add themselves or a different user to the operator role by using the ALTER ROLE command. For example, to add user dba1 to the role, the below Transact-SQL(TSQL) commands can be used. Before you run these commands, the dba1 user should already have been created in the msdb database.

use msdb
go
alter role SqlAgentOperatorRole add member dba1
go

If instead of adding users to the operator role, the master user wants to give other users the ability to add themselves to the operator role, this can be done by granting the ALTER privilege on the role. For example, to give the dba1 user, the ability to add themselves to the operator role the below commands can be used.

use msdb
go
grant alter on role::[SQLAgentOperatorRole] to dba1
go

If the master user decides to let the dba1 user allow other users to add members to the operator role, the ALTER grant can be given with the GRANT OPTION. The commands for this would be as below.

use msdb
go
grant alter on role::[SQLAgentOperatorRole] to dba1 with grant option
go

Why The Operator Role Is Handled Differently

The master user is not added to the operator role directly and is given access to add themselves or other users to the operator role because a user who is part of the operator role gets an error message when they log in to an RDS SQL Server instance via SSMS. The error looks like this:

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 choose 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 TSQL commands that are expected to work for a user who is part of the operator role however, do work. So, if SQL Server Agent is administered programmatically or via TSQL commands, the SSMS error can be safely ignored. If using the SSMS graphical user interface is important, there are two ways this can be accomplished in RDS.

Login Without The Operator Role

The first way is for the user to add themselves to the operator role after login. Any user that is part of the user role can see the SQL Server Agent menu and since the user is being added to the operator role after login, the user can see the SQL Server Agent menu and does not encounter the error. In the below screenshot, the user logged into SQL Server via SSMS and is part of the user role. So, the user can see the SQL Server Agent menu but cannot see jobs that were created by other users.

The user can now add themselves to the operator role using the commands mentioned above and then refresh the jobs list as shown below.

Now the user can see all jobs on the SQL Server instance.

After the work on the SQL Server Agent jobs is complete, the user can remove themselves from the role before logging out of SQL Server. This can be done using the below commands. This will ensure that the same process can be followed during the next login and the error message is never seen.

use msdb
go
alter role SqlAgentOperatorRole drop member dba1
go

Login With The Operator Role

If the user does not remove themselves from the operator role before logging out, they will see the SSMS error during the next login and this brings us to the second way of accessing the SQL Server Agent graphical user interface in RDS. In this case, the user is already part of the operator role and sees the SSMS error during login. When this happens, the use can just choose OK in the error message and log in to SQL Server. The SQL Agent menu doesn’t appear and below is how the user interface looks.

To get access to the SQL Server Agent graphical user interface at this point, the user drops themselves from the role by using the commands mentioned above and then they refresh the user interface at the server level. They can then see the SQL Server Agent graphical user interface but not jobs owned by other users. This is how the user interface looks.

The next thing to do is to add themselves to the operator role and refresh the jobs list – the way it was explained in the case of a login without the operator role. After this is done, they can see all jobs on the server and the interface looks like this.

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 Author

 

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.