RDS microsoft SQL admin user permission to run msdb.dbo.rds_restore_database restore command

Question

created a RDS and default user is admin backup is in s3 and added option group for SQLSERVER_BACKUP_RESTORE ,iam role also but when i run the restore command i.e  exec msdb.dbo.rds_restore_database  @restore_db_name='backupdb',
@s3_arn_to_restore_from='arn:aws:s3:::Bucketname/backupfile27022023.bak';

its showing an error as The EXECUTE permission was denied on the object 'rds_restore_database', database 'msdb', schema 'dbo'.

admin is master user but it doesn't have the execute permission. How to solve it

Answer

Hello,

The Master user should be able to perform the restore database without any issues or additional grants. Please check for any syntax issues in the restore command or would it be possible to post the IAM role & policy created hiding the sensitive info.

Whether the master user is AD authenticated or sqlserver authenticated ?

If still doesn't works, then please create new login as below and try the restore

USE master

GO

CREATE LOGIN rds_backup_operator WITH Password = 'complexpassword', DEFAULT_DATABASE=master, 
   CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE msdb

GO

CREATE USER rds_backup_operator FROM LOGIN rds_backup_operator

GO

GRANT EXECUTE ON msdb.dbo.rds_backup_database TO rds_backup_operator

GO

GRANT EXECUTE ON msdb.dbo.rds_restore_database TO rds_backup_operator

GO

GRANT EXECUTE ON msdb.dbo.rds_task_status TO rds_backup_operator

GO

GRANT EXECUTE ON msdb.dbo.rds_cancel_task TO rds_backup_operator

GO

RDS microsoft SQL admin user permission to run msdb.dbo.rds_restore_database restore command

관련 콘텐츠