sql-server - 在 Azure SQL 数据库上设置 RESTRICTED_USER

Question

我正在尝试将 Azure SQL 数据库设置为 RESTRICTED_USER 模式，但遇到了我自己无法弄清楚的奇怪错误。

Msg 5011, Level 14, State 2, Line 1User does not have permission to alter database 'YourDB', the database does not exist, or the database is not in a state that allows access checks.

According to the documentation membership in database db_owner role should be enough. User is also given ALTER DATABASE permission.

RESTRICTED_USER allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their number. All connections to the database are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused.

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options

What else is needed to make database RESTRICTED_USER?

Code to reproduce:

CREATE LOGIN YourLogin WITH PASSWORD = 'password' 
GO

-- USE YourDB;
-- switch to YourDB here, Azure SQL does not support USE statement

CREATE USER YourUser
    FOR LOGIN YourLogin
    WITH DEFAULT_SCHEMA = dbo

GRANT ALTER TO YourDB

EXEC sp_addrolemember N'db_owner', N'YourUser'

那么，下面的语句就会失败。

ALTER DATABASE YourDB SET RESTRICTED_USER;

Answer 1

我尝试重现该问题，但只有系统管理员可以将数据库状态更改为restricted_user。

您还提到文档指出数据库 db_owner 角色的成员资格应该足够了

但是文档指出，当数据库设置为restricted_user模式时，可以连接到数据库的角色

RESTRICTED_USER allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their number

综上所述，具有上述角色的用户将能够连接数据库，当设置为受限用户时，并没有说明dbmanager可以将数据库的状态更改为受限用户