SQL Agent Permissions on RDS

 RDS for SQL Server now supports running SQL Agent, which is mighty handy. However it does have a couple of quirks which require you to run things in a slightly different manner. 

The thing that caught me out for a bit was user permissions. These are documented but kind of hidden in the documentation (or I just wasn't reading it properly) so I'm going to try and clarify how they work here.

Here's the short version:

• By default, only the admin user can see the SQL Agent
• They can only see jobs which they've created
• Other users can be granted permissions to use the agent and create jobs
• Extra permissions can be granted to view jobs for all users
• These extra permissions need to be added and removed as required, otherwise the user will get an error on login

I'll repeat that last point

• These extra permissions need to be added and removed as required, otherwise the user will get an error on login

 

 Some more detail

Here's what the Object Explorer looks like when I log in as my default user. This user is a member of the server processadmin role, but still can't see the SQL Agent

So first step is to add my user to the SQLAgentUserRole role in msdb

 

use msdb
go
alter role SQLAgentUserRole add member [my user]

Now I can create jobs, but still can't see jobs created by other users

Next step is I need to grant my user alter rights on theSQLAgentOperatorRole role role in msdb. Note that I'm not adding my user to the role, but I'm granting my user permission to add myself to the role when needed. Also note that you can't grant yourself these permissions so you need to do this with the admin login.

 

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

Now when I need to see all jobs I can add myself to this group first

 

use msdb
go


alter role SQLAgentOperatorRole add member [my user]

I still can't edit the jobs, only the job owner or a sysadmin can do that, which means that usually you'll need to go in as the admin account. Even with the admin account you still need to be added to the SQLAgentOperartorRole role to see all jobs. Here's the Agent view now once my user is a member of that group

When finished you need to remove yourself from the group

 

use msdb

go

alter role SQLAgentOperatorRole drop member [my user]

If you don't do this then you get this message when logging in "Execute permission was denied on the object 'xp_regread'. When you get this it also disables you from seeing the SQL Agent. Removing from the group and refreshing will bring it back, but it's kind of annoying.

What's also annoying is that this will trigger if you refresh the server view, using F5 or using the refresh icon.

Something else to watch out for is using groups for these permissions. I was stuck for a while trying to work out why I kept getting the xp_regread error and found that a group I was a member of had the permissions assigned, so even when I removed my user from the Operator group, the permissions were still applied via the AD group