6

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?

Improve this question
4
  • 2
    Contact Amazon's support instead of trying random modifications that could make thigs worse. Is this an RDS instance of SQL Server? Self-hosted on a VM? Commented Feb 12, 2018 at 15:13
  • @PanagiotisKanavos it is a RDS instance of SQL Server. Commented Feb 12, 2018 at 15:20
  • Which means you don't have administrative access to the machine itself. Contact support. Databases don't disappear by themselves Commented Feb 12, 2018 at 15:23
  • Database name is displayed, but I don't have any permission on it. I am contacting AWS support. What makes me wonder is that end-users on my android app are able to access the database (using rest API), but desktop users aren't able. @PanagiotisKanavos Commented Feb 12, 2018 at 15:33

2 Answers 2

Reset to default
12

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks, dude it worked for me. I had the same issue.
I have the same issue with MySQL now , could you please give the steps to resolve the issue
6

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.

Improve this answer

3 Comments

Thank you so much, I would give more votes if I could. Somehow my permissions were reset or not correctly assigned when I first created the database instance on AWS. After resetting my master password on the RDS console the permissions were restored and worked perfectly.
Agree with Thabiso! You deserve more than one vote!
Agreed. Apparently resetting RDS master password grants db_owner on all databases, restoring access if lost. Thank you!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.