Master User Lost It's Permissions Unexpectedly On Sql Server (rds Instance)

SQL Server master user lost his permissions unexpectedly. When I try to access my database tables, I get the following error on SSMS: The SELECT permission was denied on the obje

Solution 1:

I was able to solve my problem after contacting AWS support.

CAUSE

The cause of my problem was that db_denydatawriter and db_denydatareader were granted to master user mistakenly.

So, master user does not have read/write permissions on your database, when trying to grant or revoke permissions on SSMS the User Mapping will not be able to list the database because SSMS can't read the properties anymore and will show the error:

One or more databases are inaccessible and will not be displayed in list

To check if you have granted db_denydatawriter or db_denydatareader to your master user, try the folloing command:

use [YourDatabaseName]
go
sp_helpuser

Changing master password won't help because that will grant db_owner permission but it won't remove the Deny permission.

SOLUTION

Use the following command to remove the Deny permission:

USE [database-name]
GO
ALTER ROLE [db_denydatareader] DROPMEMBER [master-user-name]
GO
ALTER ROLE [db_denydatawriter] DROPMEMBER [master-user-name]
GO

It worked for me, hope it helps!

Solution 2:

AWS RDS SQL Server grant dbower to master user

Incase if you accidentally revoked the permission or post restore database is not accessible by master user in SQL Server RDS then only option is to you reset the password of master user from console.

You might be thinking password resent?? why :)

I faced this issue and when I reset the password from console it automatically granted the permission to this database.