SQL Server Restricted User ?

---

Sometimes you might find your SQL Server database is in "Restricted User" mode: like following:

What does "Restricted User" means?

First, let us read MSDN information:

• Restrict Access

Specify which users may access the database. Possible values are:

• Multiple

The normal state for a production database, allows multiple users to access the database at once.

• Single

Used for maintenance actions, only one user is allowed to access the database at once.

Restricted

Only members of the db_owner, dbcreator, or sysadmin roles can use the database.

So how to disable Restricted ?

1: Run the following commands:

ALTER DATABASE database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE database SET MULTI_USER
GO

2: Using SSMS right click on the database -- properties –option in the right pane you can find restrict access

SINGLE_USER = only one db_owner, dbcreator, or sysadmin user at a time

RESTRICTED_USER = only members of db_owner, dbcreator, and sysadmin roles

MULTI_USER = all users

(OR)

SELECT DATABASEPROPERTYEX('<DBName>','UserAccess')--- (Indicates which users can access the database)

alter database <DBNAME> set multi_user