Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - SINGLE_USER/MULTI_USER - Database User Access Options

By: FYIcenter.com

(Continued from previous topic...)

How to set database to be SINGLE_USER?

Databases in SQL Server have three user access options:

  • MULTI_USER - All users that have the appropriate permissions to connect to the database are allowed. This is the default.
  • SINGLE_USER - One user at a time is allowed to connect to the database. All other user connections are broken.
  • RESTRICTED_USER - Only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number.

You can use the "ALTER DATABASE" to change database user access options as shown in the tutorial below:

USE FyiCenterComData
GO

ALTER DATABASE FyiCentercomData SET SINGLE_USER
GO

Now connect to server with another client session and try:

USE FyiCenterComData
GO
Msg 924, Level 14, State 1, Line 1
Database 'FyiCenterComData' is already open and can only 
have one user at a time.

Go back to the first session and re-set the database to MULTI_USER:

ALTER DATABASE FyiCenterComData SET MULTI_USER
GO

(Continued on next topic...)

  1. What is a database?
  2. What is the simplest way to create a new database?
  3. How to set the current database?
  4. How to delete a database?
  5. Why I am getting this error when dropping a database?
  6. How to get a list all databases on the SQL server?
  7. Where is my database stored on the hard disk?
  8. How to create database with physical files specified?
  9. How to rename databases?
  10. Why I am getting this error when renaming a database?
  11. What are database states?
  12. How to set a database state to OFFLINE?
  13. How to move database physical files?
  14. How to set database to be READ_ONLY?
  15. How to set database to be SINGLE_USER?
  16. What are system databases?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...