End User Access > Command Center > Databases > Cloud Databases > Amazon Web Services > RDS for SQL Server > Amazon RDS SQL Configuration > Creating a Non-Master User Account

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

To back up and restore Amazon RDS SQL Server databases, you can create a non-master user account with required permissions.

Procedure

1. On SQL Server Management Studio, connect to the RDS instance and run the following script on the master database:

   USE master;
   GO

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

   where, NewLoginName and newpassword can be any new user name and password for the login account

2. Grant permissions to the new login account using the following script:

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

3. Create a new user on all the existing databases, and then assign the db_owner role.

   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 can be any new user name that you want to create on the databases.

4. Assign the following permissions to the new user on the msdb system database:

   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

Last modified: 10/20/2020 2:50:53 PM