Have you ever encountered a situation where your SQL Server instance starts in single user mode every time, despite applying fixes? This can be a frustrating issue, but don’t worry, we have a step-by-step solution for you.

Understanding Single User Mode

In SQL Server, single user mode means that only one user can connect to the SQL Server instance at a time. When the SQL Server instance is started in single-user mode, it will not allow others to establish a connection to the server simultaneously. If you’ve already connected to your instance in single user mode, nobody else can make a connection to that instance, and you will receive a “Login failed for user” error message.

Checking SQL Server Startup Parameters

The first thing to check when facing this issue is the SQL Server startup parameters. The “-m” parameter starts up SQL Server in single user mode. However, if you don’t find the “-m” startup parameter in the configuration, it might not be the cause of the problem.

Checking the SQL Server Error Log

Another step to investigate is checking the SQL Server error log. Sometimes, you might find that the SQL Server database engine is starting in single user mode with the “-m” parameter. Restarting the SQL Server service might not resolve the issue, as it continues to start in single user mode.

Checking Registry Keys

If the previous steps didn’t provide a solution, it’s time to look at the registry entries for SQL Server. The sys.dm_server_registry dynamic management view (DMV) can be used to gather all registry entries related to SQL Server. By reviewing the registry keys, you might find that the “-m” parameter is mentioned in the value of the SQL Server image path. Editing the registry key and removing the “-m” parameter can make SQL Server available for multiuser access.

Restarting SQL Server

After removing the “-m” parameter from the registry entry, restart the SQL Server service. This time, SQL Server should start in multiuser mode, allowing all users to make connections to the database instance.

Summary

It’s still unclear how the “-m” parameter was added to the registry in the first place. It’s possible that it was changed in one of the GUI tools, and the registry didn’t update correctly, or someone changed the registry directly. If you have faced a similar issue, please share your views and possible causes.