AWS RDS Master user lost it’s permissions unexpectedly on SQL Server
This is the stupidest thing problem happened to me. I was trying to make a backup of the database but wasn’t able to do that. So I thought it may be permission issue.
By mistake I clicked db_denydatareader and db_denydatawriter permission checked for the user. Then I lost the security option under my database.
I was shocked what the hell I did with production database. I tried to google but no luck as I wasn’t sure what to search :). I decided to restore my backup.
After some time I decided to google again and by luck I found the an easy solution for this problem.
Solution: AWS RDS Master user lost it’s permissions unexpectedly on SQL Server
- Login to your SSMS.
- Right click on the database you lost the permission and select “New Query”
- Copy and paste below code. Replace database name and username with your database and username.
- You are good now. Just refresh the database and you can read and write now.
USE [database-name]
GO
ALTER ROLE [db_denydatareader] DROP MEMBER [master-user-name]
GO
ALTER ROLE [db_denydatawriter] DROP MEMBER [master-user-name]
GO
