Everybody knows local temporary tables have scope only within a connection. Drop the connection and it goes away. The connection is at the server level, not at the database level. People commonly say things like "I'm connected to the database" when they are actually connected to the server. In SSMS, you right-click on a specific database and select the option to open a new query window. Since you initiation the connection from a specific database name in the Object Explorer, it does seem like the database owns the connection. But the database doesn't own the connection in any way.
When you have a connection to a user database and create a local temporary table, the temporary table is created in tempdb. It's true that queries issued against your user database can access the temporary table, but they aren't related to each other, they are only sharing the same server connection. You can drop your database completely, but it does not affect any temporary objects you created in tempdb.
Creating a different user database doesn't change anything if all of the work is done within the same query window in SSMS. Although you are attempting to create a temporary table from a different user database, the fact remains that tempdb is the same and is being referenced from the same connection. Referring to the code sample below, the fact that it seems like you issued the second create table statement "from" database DEMO2 is irrelevant. The temporary table is issued from the server connection, not from the database you issued the USE statement against.
A user database named DEMO1 is created and a USE statement sets the query context to the DEMO1 database. Next, a temporary table is created. After that, the user database is dropped. Because your connection to tempdb persists, your temporary table persists. The point is that although your connection's context is the DEMO1 database, the temporary table really doesn't have anything to do with DEMO1. Although it arguably appears to have been created from demo1, it was created on the server in tempdb. The USE DEMO1 statement is irrelevant, it doesn't affect temporary objects in tempdb. The DEMO1 database doesn't in any manner have ownership of temporary objects in tempdb. Only the connection to the server owns the temporary objects. If you want to be able to create that local temporary table a second time without seeing an error message, you'll either have to drop it first or open a completely new query window in SSMS. If you don't, you'll see something like this:
Msg 2714, Level 16, State 6, Line 2
There is already an object named '#demo' in the database.
Thanks to a reader for pointing out corrections to this post. Here's some code you can play with to see for yourself.
use master
go
create database demo1 on primary
( name = demo1, filename = 'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo1.mdf' ) -- alter filenames for your environment
log on
( name = demo1_log, filename = 'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo1_log.ldf' )
go
use demo1
go
create table #demo (a int) -- succeeds
go
use master
go
alter database demo1 set single_user with rollback immediate
drop database demo1
go
create database demo2 on primary
( name = demo2, filename = 'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo2.mdf' )
log on
( name = demo2_log, filename = 'c:\program files\microsoft sql server\mssql10.gdt\mssql\data\demo2_log.ldf' )
go
use demo2
go
create table #demo (a int) -- fails
go
use master
go
alter database demo2 set single_user with rollback immediate
drop database demo2
go