Create a Non-Master User Account for Amazon RDS SQL Database Backups

If you don't want to use the master account to back up the Amazon RDS for SQL Server instance, create a non-master user account that has the required permissions.

Procedure

1. In SQL Server Management Studio, connect to the RDS instance.

2. On the master database, to create the new account, run the following script:

   USE master;
   
   GO

   CREATE LOGIN [NewLoginName] WITH PASSWORD = N'newpassword',
   
   DEFAULT_DATABASE=master,
   
   CHECK_POLICY = OFF,
   
   CHECK_EXPIRATION = OFF;
   
   GO

   where NewLoginName and newpassword are the user name and password for the new account.

3. To grant permissions to the new login account, run the following script:

   GRANT CREATE ANY DATABASE TO [NewLoginName] WITH GRANT OPTION;
   
   GRANT VIEW ANY DATABASE TO [NewLoginName] WITH GRANT OPTION;
   
   GO

4. To create a new user on all the existing databases and assign the db_owner role, run the following script:

   EXEC sp_MSforeachdb '
   
   DECLARE @name VARCHAR(500)
   
   SET @name=''?''
   
   IF DB_ID(@name) > 5
   
   BEGIN
   
   USE [?]
   
   CREATE USER NewUserName FOR LOGIN NewLoginName;
   
   EXEC sp_addrolemember ''db_owner'',''NewUserName''
   
   END'

   where NewUserName is the new user name that you want to create on the databases.

5. To assign the required permissions to the new user on the msdb system database, run the following script:

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