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