SQL SERVER back up issue

Try to fix by the following:

Option 1: Use a Sysadmin Account Make sure you're logged in with an account that has sysadmin privileges. This bypasses the permission issue entirely. Option 2: Grant SELECT Permission If you must use a lower-privileged account, a sysadmin can run this:

GRANT VIEW SERVER STATE TO [YourLoginName]

This allows access to dynamic management views like dm_os_file_exists. Btw, please note that you cannot directly grant SELECT on dm_os_file_exists because it's part of the hidden mssqlsystemresource database. VIEW SERVER STATE is the workaround.

It looks like you're encountering a permissions issue when trying to create a .bak file using SQL Server Management Studio (SSMS) UI on an AWS RDS SQL Server instance.

The error indicates that you don't have the SELECT permission on the 'dm_os_file_exists' object, which is needed for the backup operation. This is expected behavior because in Amazon RDS for SQL Server, you don't have direct access to the file system or certain system objects.

For RDS SQL Server instances, you need to use the native backup and restore functionality provided by AWS instead of the standard SSMS backup UI. Here's how to properly create a backup:

1. First, ensure you have set up the SQLSERVER_BACKUP_RESTORE option in your RDS option group.
2. Make sure you have an IAM role with appropriate S3 permissions (s3:ListBucket, s3:GetBucketLocation, s3:GetObjectAttributes, s3:GetObject, s3:PutObject, s3:ListMultipartUploadParts, s3:AbortMultipartUpload).
3. Ensure your S3 bucket is in the same AWS Region as your RDS instance.
4. Instead of using the SSMS UI, you need to run the following SQL command:

exec msdb.dbo.rds_backup_database
@source_db_name='your_database_name',
@s3_arn_to_backup_to='arn:aws:s3:::your_bucket_name/your_backup_filename.bak',
@overwrite_S3_backup_file=1;

Also, make sure that your user has the appropriate role memberships. You need to be a member of either the db_owner or db_backupoperator role in the source database to perform backups. If you're not, you can add your user to the appropriate role with:

USE [your_database_name];
EXEC sp_addrolemember 'db_backupoperator', 'your_username';

Remember to connect using the master user account or a user with sufficient privileges when executing these commands.
Sources
Native Backup And Restore Option | AWS re:Post
I want to backup microsoft SQL database to RDS. I have setup all prerequisites now I am having error while msdb.dbo.rds_backup_database command | AWS re:Post