We have a single RDS instance for MS SQL. I have been successfully using a third-party SQL backup tool to perform native SQL backups. To achieve this I added GRANT BACKUP DATABASE permissions to the Active Directory user that runs the backup job for each database in the instance.
This has been working fine until I recently created a new database in the instance. My user account has DENY BACKUP DATABASE permissions and of course, that means I can't grant that permission to any other user.
When I try to grant permissions it gives me the error below:
Msg 2745, Level 16, State 2, Procedure rds_deny_backups_trigger, Line 34 [Batch Start Line 0]
Process ID 84 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Procedure rds_deny_backups_trigger, Line 34 [Batch Start Line 0]
You cannot GRANT/REVOKE 'BACKUP DATABASE' or 'BACKUP LOG' permission for a user.
Is the rds_deny_backups_trigger something new?
Is there a way to grant BACKUP DATABASE permissions for a user now?