How to Change the SQL Server Database Mode?

If required, SQL server database administrator can change his/her database’s mode by using SQL server enterprise manager, SQL server management studio and SQL commands like ALTER database or sp_dboption. SQL server enterprise manager is a Microsoft management console that provides user interface for DBAs to perform several tasks like create database, define groups, register server etc. DBA can use it to change the database mode in single user mode or in multi user mode for SQL server 2000. Similarly SQL server management studio can be used for changing the database mode for SQL server 2005 and all upper versions.

What is Database Mode?

Database mode specifies your database access permission like how many users can access your database at time. Suppose your database is in single user mode then it specifies that only one user can access the database at a time and generally it is used for the database maintenance purpose. Your database can be used by multiple users at a time when database is in multi user mode.    

Change the SQL database mode using ALTER command:

ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [Works] SET SINGLE_USER WITH NO_WAIT

Change the SQL database mode using stored procedure:

EXEC sp_dboption 'Works', 'single user', 'false'

EXEC sp_dboption 'Works', 'single user', 'true'

Change the SQL database mode using SQL server management studio: To change the database mode using SQL server management studio, first open it and connect to the SQL server instance. Now open the databases belonging to the instance in object explorer windows and right click on your desired database and click on the properties menu. When you click on the properties menu, database properties will be displayed at the screen like below image.

As you see in the above screen, there are three options: single, multiple, & restricted. I have already discussed these modes in the above section. When you choose single from the list to change the database mode either you will successfully change the mode or will get fail due to existence of active connection to the desired database. SQL server prompts you to close all the active connection from the database before changing the database mode. After closing all the connection, you will be able to change the database mode in single_user mode. Similarly you will change the database mode in multi_user mode of your desired database.