Sql-server – Setting a SQL Server instance back to multi user mode

configurationsql server

I had to restore a database, but some of the users were still connected, and had left for the day. Previously, I had set the database to single user, now I do not see that option.

So, I made a change at instance level, setting Properties/Connections/Maximum number of concurrent users to 1, disconnecting all other users from the server.

I restarted the SQL Server service, but now I am unable to access the database at all. The database is up and running, but I can't open a query window or right click on Properties to revert the change.

How can I fix this?

Best Answer

First thing, never set Max Number of Concurrent Connections on the server properties page to 1. This option can even prevent administrators logging in, but in any case the Dedicated Administrator Connection can still be used.

  1. Restart services using SQL Server Configuration Manager, try to login and if not successful, use the Dedicated Administrator Connection.
  2. Right click on the Instance name, and change the value for Max Number of Concurrent Connections to 0 (which means maximum) and restart services.

Sometimes, after setting Max Number of Concurrent Connections to 1, on login you may face the following error message:

Cannot open user default database. Login failed.
Login failed for user ‘UserName’

To fix this: In the login window, click on the Options button and in the default database item, select a database like tempdb and then press login.

If the database is in Single_User mode

Using T-SQL:

USE [master];
GO
ALTER DATABASE [YourDatabaseNameHere] SET MULTI_USER WITH NO_WAIT;
GO

Using SSMS:

  • Right click on required database --> Properties
  • On the left of Database properties window, click on Options
  • On the righthand side, scroll down and at bottom you will find a status section
  • Change the Restrict Access value to multi_user

If the database is in Single_User mode, and you want to change it to Multi_User, but can't access it as someone else already have the connection, then:

  1. If you have ALTER ANY CONNECTION rights (part of sysadmin and processadmin fixed server roles), run SP_WHO or SP_WHO2, find the process using the target database and execute:

    KILL processid (Ex: KILL 87)

    then execute above T-SQL to bring your database back to multi_user.

  2. Or, you can detach and then attach your database and execute the T-SQL above to bring it to multi_user. This is not recommended on production servers.

Related Question