CODEWITHSUNDEEP
sql-serverdatabaseazurepermissions
Eugene D. Gubenkov
Eugene D. Gubenkov

Reputation: 5357

Set RESTRICTED_USER on Azure SQL database

I'm trying to set Azure SQL database to RESTRICTED_USER mode and facing the odd error I was unable to figure out myself.

Msg 5011, Level 14, State 2, Line 1
User 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'

Then, the following statement fails.

ALTER DATABASE YourDB SET RESTRICTED_USER;

Upvotes: 1

Views: 949

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28890

I tried to Repro the issue and only sysadmin could change the database state to restricted_user.

Also you have mentioned that documentation states membership in database db_owner role should be enough

But documentation states ,the roles which can make connection to the database ,when database is set to restricted_user mode

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

In summary ,users having above said roles will be able to connect to database ,when it is set to restricted user,it doesn't state,that dbmanager can change the state of database to restricted user

Upvotes: 1

Related Questions