Single user restrictions even if nobody's connected

Question

When using SQL Server Management Studio I try to query a DB created with "Single user" restrictions.

I log in by using SQL Credentials and "sa" user and by running sp_who2 command I double check nobody is connected to the DB, nevertheless when executing a query on a DB table, the system replies:

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

I'd like to understand why I am experiencing this issue. Can anybody help on this?

Answer 1

When you set a db in "single user" mode you can only open a single connection to the database (more info here).

So when you open SSMS to navigate through database objects you open the only permitted connection. After this any other operation that requires a new connection to the db will fail, for example creating a new query window to write a query.

From MS Docs:

When you start an instance of SQL Server in single-user mode, SQL Server Management Studio can connect to SQL Server. Object Explorer in Management Studio might fail because it requires more than one connection for some operations. To manage SQL Server in single-user mode, execute Transact-SQL statements by connecting only through the Query Editor in Management Studio, or use the sqlcmd utility.

The same happens on premise if you have the SQL Server agent running.

Another advice from MS Docs (if you are running SQL Server on premise):

Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.