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

Posted by | View Post | View Group