I'm having an issue in a production environment and I was trying to simulate it in my local SQL Server Express instance.

I used SSMS to change the "maximum number of concurrent connections" from "0" (max = 32767) to "2".

After restarting the service, I'm able to connect to it with SSMS but when I try to open Server Properties (or "New Query" window) to change the setting back to "0", SSMS crashes.

Is there any other place I can change this setting without SSMS (e.g. registry)?

3 Answers 3


This should do it:

EXEC sp_configure 'user connections', 0;

That will set the number of connections back to the default, which is 32767.

Once you have SSMS open, disconnect from the server in the "Object Explorer" Window. Then you should be able to create a new query window and connect to it there. Alternately, you could use sqlcmd.exe to connect from the command-line without having SSMS open, then run the command from there.

The setting is stored in the master database, and can be seen through the following statement:

FROM sys.configurations c
WHERE c.name = 'user connections';

sys.configurations is actually a view, defined as:

CREATE VIEW sys.configurations AS
    SELECT configuration_id,
    FROM sys.configurations$
    WHERE is_not_use = 0

The only way to see the sys.configurations$ table is through the Dedicated Admin Connection, more commonly known as the DAC. The DAC needs to be turned on through SQL Server Configuration Manager, since it is off by default in SQL Server Express.

  • 1
    Ok, but how can I execute it without being connected to it through SSMS? I can neither open Server Properties nor New Query window...
    – Alexandre
    Mar 23, 2016 at 19:58
  • Closing connection in "Object Explorer" window and opening "New Query" window didn't solve it, because it still pointed no more connections available. But sqlcmd.exe did the trick. Thank you.
    – Alexandre
    Mar 23, 2016 at 20:13
  • I'm still just curious about where do those settings are stored...
    – Alexandre
    Mar 23, 2016 at 20:18
EXECUTE sys.sp_configure 
    @configname = 'show advanced options',
    @configvalue = 1;


EXECUTE sys.sp_configure 
    @configname = 'user connections',
    @configvalue = 0;


If you cannot open a query window in SSMS, change the SSMS startup option (via Tools -> Options) to open an empty environment:

SSMS Tools -> Options

Beyond that, there are more options, including starting SQL Server in single user mode for a specific application, and connecting using that (e.g. the sqlcmd utility).

The setting of user connections is not stored in the registry. It is in a system table in the master database, and is not user-editable. The only way to change it is by using the tools provided e.g. sp_configure.


You have several options

  • SSMS (yes I know you don't want to use SSMS & I'll get to that in a second). Just make sure you aren't using object explorer and just use a query window.

    EXEC sp_reconfigure 'user connections',0;
  • SQLCMD - This is a command shell utility that will let you run SQL Server scripts. Run the SQLCMD command from a command shell. The blank lines are just carriage returns.

    SQLCMD -E -S ServerName
    1> EXEC sp_configure 'user connections',0;
    5> GO
  • Powershell - I have no idea how to do this myself but I'm sure a quick bing/google search would do it for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.