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
- On SQL Server Management Studio, connect to the RDS instance and run the following script on the master database:
USE master;
GOCREATE LOGIN [NewLoginName] WITH PASSWORD = N'newpassword',
DEFAULT_DATABASE=master,
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;
GOwhere, NewLoginName and newpassword can be any new user name and password for the login account
- 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 - 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.
- 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