Single user mode looses connection So just a quick background, we are trying to update the database design, in a production environment. But we want to be sure, no users try to login during that time. So we started looking into single user mode, but that gave us some trouble, sometimes we would lose the connection in the middle of the update.
So we setup a test environment to replicate the behavior.
We are using Microsoft SQL server 2017, with the AdventureWorks2017 database to replicate the issue.
On the database we have turned off Auto close and Auto Update Statistics Asynchronously
If we then have two connections to the server, using the master database.
Tell one of them to run this script
USE MASTER
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [AdventureWorks2017] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses 
WHERE spid &gt; 50 AND dbid = DB_ID('AdventureWorks2017')
EXEC(@kill);

USE AdventureWorks2017
GO

DECLARE @cnt INT = 0;
WHILE @cnt &lt; 10000
BEGIN
  SELECT TOP 1000 * from Person.Person;
   SET @cnt = @cnt + 1;
end;

And then on the other repeatedly run
SELECT TOP 1000 * FROM AdventureWorks2017.Person.Person;
GO;

At some point the first script stops working, and complains with an error

Database 'AdventureWorks2017' is already open and can only have one user at a time.

But to our understanding, this should not happen cause it still has the connection.
Note this doesn't happen all the time. But it's still fairly consistent.
Is there anything that we are missing, or can this be an issue with the SQL server?
When you set a database to single_user, you don't have any control over which session gets to connect, unless you're already connected to the database.
So simply connect before setting single_user to eliminate the race condition:
USE AdventureWorks2017
ALTER DATABASE CURRENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE