Loading...

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