Single User Mode on SQL Server Management Studio

Copper Contributor

We have an instance where the resource for our SQL Database was updated, when we try and sign into SQL Server Management it just says Single User Mode and only one admin can be signed in at any one time - but there is only one trying to sign in.


We've tried adding ;-m and -m into the start up parameters when trying to sign into SQL Management, but to no avail., we've re-applied the correct resource via cmd and then tried again nothing.


Is anyone able to assist with some suggestions?



2 Replies



Hi, Sam.


Assuming you have the necessary rights on the SQL Server, you can use the following query to determine which login, from which host, under which process is claiming the single connection.


Anecdotally, I tend to find it's something unattended like a Window Service that claims the single connection in these scenarios.



In this example, I'm checking the details on who has the connection to a database named "throwaway".


	, s.[login_name]
	, db.[name]
	, s.[host_name]
	, s.[host_process_id]
	, s.[program_name]
	sys.dm_exec_sessions AS s INNER JOIN
	sys.databases AS db ON
		s.database_id = db.database_id
	db.[name] LIKE 'throwaway%';


Example output

In the output we can see who/what is connected, from which host and which program/process they're running that has claimed the single connection.




Once you've kicked the person/process off (i.e. temporarily stopping the Windows Service, sticking with my earlier example), you can then change the mode back to multi_user - assuming nothing else tries connecting after the first claimant has been dealt with (i.e. this could become a circular issue for you).





1. SSMS = "SQL Server Management Studio" is just a tool, it can't be in "single user mode"; the database engine may is.

2. SSMS always open 2 connections to SQL Server, one to many. Close the "Object Explorer", which holds a separate connection.

3. Review SQL Server ErrorLog file for details, why SQL Server is in single-user mode; it's a simple text file.

Viewing the SQL Server Error Log - SQL Server | Microsoft Learn