主用户在SQL Server(rds实例)上意外丢失了它的权限 [英] Master user lost it's permissions unexpectedly on SQL Server (rds instance)

问题描述

SQL Server主用户意外丢失了权限.当我尝试访问数据库表时，在SSMS上收到以下错误:

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

对对象'query_store_runtime_stats'，数据库'mssqlsystemresource'，模式'sys'的SELECT权限被拒绝. (Microsoft SQL Server，错误:229)

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.

我尝试使用sql查询授予权限，但即使该特权也被禁用.

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.

我在Amazon AWS控制台上更改了主密码，但这并不能解决我的问题.

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

我应该怎么做才能解决这个问题?

What should I do to solve this issue?

推荐答案

联系AWS支持后，我能够解决我的问题.

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

原因

我的问题的原因是 db_denydatawriter 和 db_denydatareader 被错误地授予了主用户.

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

因此，主用户对您的数据库没有读/写权限，因此当尝试授予或撤消对SSMS的权限时，用户映射将无法列出数据库，因为SSMS无法再读取属性并显示错误:

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

要检查您是否已将 db_denydatawriter 或 db_denydatareader 授予主用户，请尝试以下命令:

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

use [YourDatabaseName]
go
sp_helpuser

更改主密码无济于事，因为这将授予db_owner权限，但不会删除拒绝"权限.

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

解决方案

使用以下命令删除拒绝"权限:

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!

这篇关于主用户在SQL Server(rds实例)上意外丢失了它的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！