Why is my auto-restore procedure not dropping the databases when I expect it to?
What's probably happening is that another session is connecting to the database after you set it to SINGLE_USER.  Always connect to a database before you set it to SINGLE_USER, or you can't guarantee that you will be the single user.  But you can't drop a database you're connected to, so SINGLE_USER is just not the right state.

While setting the database OFFLINE will work, be aware that dropping an OFFLINE database does not delete the database files.  Then you won't be able to RESTORE because the database files are hanging around.  And you don't have rights to delete database files, by default, and shouldn't really need them.

RESTRICTED_USER is probably the best state to put a database ahead of dropping it.  eg


  RESTRICTED_USER
  
  Allows for only members of the db_owner fixed database role and
  dbcreator and sysadmin fixed server roles to connect to the database.
  RESTRICTED_USER doesn't limit their number. Disconnect all connections
  to the database using the timeframe specified by the ALTER DATABASE
  statement's termination clause. After the database has transitioned to
  the RESTRICTED_USER state, connection attempts by unqualified users
  are refused.


https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017

use master
alter database foo set restricted_user with rollback immediate
drop database foo