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.
- Restart services using SQL Server Configuration Manager, try to login and if not successful, use the Dedicated Administrator Connection.
- 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
ALTER DATABASE [YourDatabaseNameHere] SET MULTI_USER WITH NO_WAIT;
- 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:
If you have
ALTER ANY CONNECTION rights (part of sysadmin and processadmin fixed server roles), run
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.
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.