How do I create a SQL user with access to create RDS backups?

I'd like to automate RDS backups, but I don't want to use the sa user for backups. Can I create a user that only has access to create backups and view the status of backups?

Answer

You can, here's how:

First, Create a login for the RDS-only user (use a strong password here):

 USE master 
 GO 
 CREATE LOGIN rds_backup_operator WITH Password='********', DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
 GO 

Then grant that user access to execute the stored procedures used by RDS backup (this must be done on the msdb database):

 USE msdb 
 GO 
 CREATE USER rds_backup_operator FROM LOGIN rds_backup_operator 
 GO 
 GRANT EXECUTE ON msdb.dbo.rds_backup_database TO rds_backup_operator 
 GO 
 GRANT EXECUTE ON msdb.dbo.rds_restore_database TO rds_backup_operator 
 GO 
 GRANT EXECUTE ON msdb.dbo.rds_task_status TO rds_backup_operator 
 GO 
 GRANT EXECUTE ON msdb.dbo.rds_cancel_task TO rds_backup_operator 
 GO 

Finally, create a user for the login you created in the database(s) you want to backup, and add that user to the db_backupoperator role. This must be done on the database(s) you want to backup:

 USE __DATABASE__ 
 GO 
 CREATE USER rds_backup_operator FROM LOGIN rds_backup_operator 
 GO 
 ALTER ROLE db_backupoperator ADD MEMBER rds_backup_operator 
 GO 

This use will be able to create / restore / cancel / and view status of RDS backups, but nothing else.