SQL Server Single User Mode Connection with SQL Server Management Studio

By:   |   Comments (1)   |   Related: > SQL Server Management Studio


Sometimes it's necessary to start SQL Server in single-user mode, like for recovering system databases or changing server configuration options.

In single-user mode, only one user from the administrator's group of the server can connect as a sysadmin fixed server role member. To connect and execute T-SQL commands in single-user mode, both SQL Server Management Studio (SSMS) and sqlcmd utility can be used. However, many users are used to using a graphical interface and feel more comfortable with SSMS rather than working with the command line. In turn, using SSMS in single-user mode can present some difficulties. In this article, we are going to describe and solve a specific problem when a user connects to the server in a single-user mode from SSMS and receives the error that only one user can connect in single-user mode as shown below.

single user mode error

If you're the only one connecting to SQL Server why would you still get this error "Login failed for user... Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)".

Let's reproduce the error and then suggest a solution. Let's assume that we need to connect to the SQL Server Service in single-user mode to perform a specific operation which is only possible in this mode, such as restoring the master database.

Put SQL Server Instance in Single User Mode

To start SQL Server in the single-user mode, we add the corresponding startup parameter -m using "SQL Server Configuration Manager".

To do this, open "SQL Server Configuration Manager", choose "SQL Server Services", then choose the corresponding SQL Server instance, right-click on it and choose "Startup Parameters". As a startup parameter, we specify "-m" that means that the service will start in single-user mode.

sql server configuration manager

Click "Add" and then "Apply" and the following message will appear.

sql server configuration manager

As we can guess, this means that the service restart is required to start the server with the changed option. So, we click "OK" on the warning window and then "OK" to close the "Properties" window. Then, we restart the SQL Server service.

sql server configuration manager

Connect to SQL Server in Single User Mode with SSMS Error

After the restart, open SSMS and try to connect. We receive the same error.

sql server mangement studio

Thus, it is clear from the error message that there is an existing connection to the SQL Server and therefore other connections are not possible.

Let's understand which service could make a connection to the SQL Server. When we open "SQL Server Configuration Manager", we can see that the "SQL Server Agent" service is running.

sql server configuration manager

The agent could be using the only available connection to the SQL Server, so let's eliminate this and stop the agent and restart the SQL Server again.

sql server configuration manager

After stopping SQL Server Agent, we try to connect again with SSMS and now we are able to connect to the SQL Server.

Restore Master Database with SSMS in Single User Mode

Now that we are connected via SSMS in single user mode, let's try to restore the master database.

Restore with SSMS GUI

We execute a restore as normal via SSMS as shown below.

sql server mangement studio

However, we end up getting the same error message again.

single user mode error

The reason for this is that "Object Explorer" has already made a connection and in turn the restore task tries to initiate a new connection and therefore fails to connect.

Restore with a Query Window

Well, let's try to restore using T-SQL code instead of the visual interface.

To perform this, we try to open a new "Query Window", but this also fails, because it tries to open a new connection.

sql server mangement studio

Hence, we understand that we need to eliminate all possible connections to the SQL Server to be able to connect in the single-user mode. As we saw, SQL Server related services, such as SQL Server Agent, could use this one connection. Additionally, the "Object Explorer" and a "Query Window" also issue a new connection to the server.

Making a Successful Query Window Connection

Note: you could restart SQL Server again to make sure there are no remaining connections.

To avoid multiple connections, we will open "SQL Server Management Studio", but without connecting to the server. In other words, we will close the "Connect to Server" window, and also close "Object Explorer".

Then we will click on "New Query".

sql server mangement studio new query

From this new query window, we will connect to the database.

connect to sql server

At this time, the connection is successful. This means that we managed to avoid multiple connections to the server and kept the single connection for our query window.

Now, we can execute our T-SQL restore command in the query window and restore the master database.


Hence, our problem is solved – we used the only connection in single-user mode to connect to the server from SSMS.

Other Notes

It is important to mention that we can use –m<Client Application Name> startup option to allow only the specified application to connect in single-user mode. So, -m"Microsoft SQL Server Management Studio - Query" can be used as a startup parameter to connect in single-user mode only from the "Query Editor".

Having said that, this startup option cannot be used with "SQL Server Configuration Manager" because it uses a dash character which is not allowed in this tool, so this can only be done via the command line to start SQL Server this way.


As this article is aimed at helping those who prefer to use SSMS instead of the command line, we have discussed the option which allows us to change the configuration from the "SQL Server Configuration Manager" visual tool.


It is possible to connect to SQL Server in single-user mode only if there are no other connections to the server. Therefore, it is important to be sure that all services that can connect to SQL Server, such as SQL Server Agent, are stopped. Additionally, in the case of connecting from SSMS, it is essential to eliminate all other connections from SSMS. Closing the "Object Explorer" and connecting by opening just one query window can be a solution.

After this, you can remove the startup parameter and restart SQL Server in multi-user mode.

Next Steps

Additional topic-related information can be found in the following links:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, December 24, 2021 - 6:58:20 AM - Anusha B Back To Top (89614)
Thank you. It worked for me :)