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:
The normal state for a production database, allows multiple users to access the database at once.
Used for maintenance actions, only one user is allowed to access the database at once.
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
SELECT DATABASEPROPERTYEX('<DBName>','UserAccess')--- (Indicates which users can access the database) alter database <DBNAME> set multi_user