SQL Server table name starting with # in user database, not in tempdb, not a temp table
Given:


sp_rename uses object names instead of object IDs,
we cannot use the object name since it starts with a # and that is interpreted as having special meaning and is handled differently,
all other options have been exhausted


You should try editing the underlying system catalog table directly via a Dedicated Admin Console (DAC) connection:


Get the object_id of that table.
Restart the instance in Single-User mode. This is to be able to directly update the system tables (i.e. not a requirement for using the DAC connection).
Connect via Dedicated Admin Console connection. You can do this in a SQLCMD interactive session by running the following in a Command Prompt window:

C:\&gt; SQLCMD -A -E


or, connect directly to the DB using:

C:\&gt; SQLCMD -A -E -d {database_name}

In that DB, try something like the following:

UPDATE sys.objects$ {enter}
SET [name] = N'obsolete' {enter}
WHERE [object_id] = {ye_olde_object_id}; {enter}
GO {enter}


It won't execute the statement until you put in the GO {enter}.


Please be cautious when directly editing system catalog tables, and do not get too comfortable with the idea of doing it. This is something that should be done only if there is absolutely no other way of fixing a problem (such as the case here). There are probably several reasons to avoid making direct edits, but the two that initially come to mind are:


Much like the data-models that we create, there are likely rules and workflows for how things work that we are unaware of (e.g. denormalization, "business" rules governing the state of data across various tables, etc)
Quite likely making direct edits voids Microsoft's responsibility to assist you if you run into problems and have a support contract (I have not seen the terms of the Support agreement(s), but I have a hard time believing that such language would not be in there)

@Paul Randal confirmed in a comment on a related answer of mine: "manually editing a system table irrevocably sets a flag in the boot page of the database that marks your database as having been edited in this way, and CSS might decide to not help you if you subsequently have problems with that database."