SET SINGLE_USER WITH ROLLBACK IMMEDIATE only disconnects sessions when account is a database user

Question

The following code creates a user that is able to restore a database:

CREATE LOGIN RestoreUser WITH PASSWORD = 'MyPassword'
ALTER SERVER ROLE dbcreator ADD MEMBER RestoreUser

This works fine and the user can restore the database, however if I want to set the database to SINGLE_USER and rollback any existing connections, the restore command is blocked and fails:

Session 1

/* SELECT from a table and leave the SSMS window open, leaving a sleeping SPID */
USE AdventureWorks2014
SELECT * FROM Person.Person

Session 2

EXECUTE AS LOGIN = 'RestoreUser'

ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE AdventureWorks2014 FROM DISK = 'C:\Test\AW14.bak' WITH REPLACE

REVERT

Eventually, session 2 times out with

Msg 5061, Level 16, State 1, Line 3
ALTER DATABASE failed because a lock could not be placed on database 'AdventureWorks2014'. Try again later.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Msg 3101, Level 16, State 1, Line 5
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

The following sp_whoisactive screenshot shows that the command is blocked:

The sp_whoisactive lock report for session 59 shows

<Database name="AdventureWorks2014">
  <Locks>
    <Lock request_mode="X" request_status="CONVERT" request_count="1" />
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
    <Lock request_mode="U" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="(null)">
      <Locks>
        <Lock resource_type="DATABASE.BULKOP_BACKUP_DB" request_mode="U" request_status="GRANT" request_count="1" />
        <Lock resource_type="DATABASE.BULKOP_BACKUP_LOG" request_mode="S" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

The Microsoft Article for SET SINGLE_USER states

To quickly obtain exclusive access, the code sample uses the termination option WITH ROLLBACK IMMEDIATE. This will cause all incomplete transactions to be rolled back and any other connections to the AdventureWorks2012 database to be immediately disconnected.

and

Requires ALTER permission on the database.

My RestoreUser account has the correct permissions (dbcreator server role gives ALTER ANY DATABASE permissions) but I don't know why the command is blocked, the first quote suggests all other connections would be disconnected.

I also granted ALTER ANY CONNECTION to RestoreUser but that did not help.

What I found did fix the problem, is if RestoreUser is a user in the database being restored (doesn't need any permissions within the database)

CREATE LOGIN RestoreUser WITH PASSWORD = 'ABC@123'
ALTER SERVER ROLE dbcreator ADD MEMBER RestoreUser

USE AdventureWorks2014
CREATE USER RestoreUser

The code

EXECUTE AS LOGIN = 'RestoreUser'

ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE AdventureWorks2014 FROM DISK = 'C:\Test\AW14.bak' WITH REPLACE

REVERT

Then runs without being blocked by the sleeping session.

My questions are

• Why does the SET SINGLE_USER WITH ROLLBACK IMMEDIATE code get blocked when it should kill and rollback all open SPIDs
• Why does creating the user in the database fix this issue?

Answer 1

As Dan Guzman originally noted, the sp_WhoIsActive output indicates the RESTORE is blocked, not the ALTER DATABASE command. Even so:

If the SET SINGLE_USER command is uncontended, there is no issue. The connection acquires the single SESSION level shared database lock that prevents anyone else connecting to the database, and life is good.

If there is contention (another connection to the database), there is a check to see if the security principal executing the command can connect to the database.

When this check fails, the ALTER DATABASE command fails and an error message like the following is returned:

Msg 5061, Level 16, State 1, Line 10 ALTER DATABASE failed because a lock could not be placed on database 'AdventureWorks2017'. Try again later.
Msg 5069, Level 16, State 1, Line 10 ALTER DATABASE statement failed.

The check succeeds when:

• The principal has CONNECT ANY DATABASE permission; or
• The principal has a related user in the database; or
• The guest user in the database has CONNECT permission.

I don't know why this check exists, or why it only occurs if there are blocking connections. There might be a good reason, or it might be an inaccurate or obsolete test.

In some ways, it is odd that a session can acquire the single SESSION shared database lock associated with single user when the principal has no right to connect to the database.

Note also the same error occurs for the same reason if you try to set the database to MULTI_USER when it is already in that state and there are other users connected to the database.

On the other hand, one could argue that ALTER ANY DATABASE does not quite fulfil the requirement you quoted for ALTER permission on the specific database. Having ALTER permission on the database implies a user in that database because that permission can only be assigned to users, not logins. That is quite persuasive, but doesn't explain why it should only be tested when contention occurs.

All that aside, the the ALTER DATABASE command in your example does throw an error, but you don't check for it, or test the database is in single-user mode before continuing with the restore.