Master user lost it's permissions unexpectedly on SQL Server (rds instance)

Question

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 object 'query_store_runtime_stats', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

If I try to give permission to master user, I get the following error when mapping credentials (Security > Logins > Masteruser > Properties):

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

The missing database is the one where my tables/procedures are stored. The end-users are unable to access the database too.

I have tried to grant permission using sql query but even that priviledge is disabled.

The only one user that is able to use that database is a read only user that i have created previously.

I changed my master password on Amazon AWS console, but that didn't solve my problem.

What should I do to solve this issue?

Answer 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] DROP MEMBER [master-user-name]
GO
ALTER ROLE [db_denydatawriter] DROP MEMBER [master-user-name]
GO

It worked for me, hope it helps!

Answer 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.