I've inherited a SQL Server Web Edition RDS instance from another firm whereby they took a snapshot and then shared it with our firm's RDS account.
After reinstating the instance under our account, I wanted to set up native backups but ran in to permissions issues when running the msdb.dbo.rds_backup_database command:
The EXECUTE permission was denied on the object 'rds_backup_database', database 'msdb', schema 'dbo'.
Believing this was due to permissions issues on the account that was given to me, I reset the used the "New master password" functionality within the RDS console to reset the master password. After I was notified that succeeded, I attempted to login but got a login failed error.
In looking at the results from the following query,
select * from master.sys.server_principals I can see that the master login is disabled.
When running the following query,
select suser_sname(owner_sid) as 'Owner', state_desc, * from sys.databases
I can see that all the databases were created with the master account.
How would I enable the master user's login in this case or does anyone know of a workaround?