Error: Cannot drop schema 'user' because it is being referenced by object '___12345679'.

Question

I have followed this thread by alter schema and transfer user and then drop the schema username but it still shows the same error. I also saw this thread in Stackoverflow but the solution there isn't very useful. I ran the sql statement below and it does result in some objects but not the one that's showing in the error.

SELECT OBJECT_NAME(ac.object_id) FROM sys.all_columns ac WHERE name = 'schema_id'

I came across this thread and ran their sql statement below but there is no result showing.

SELECT 'ALTER SCHEMA dbo TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'upstreamgasmgmt'
AND (DbObjects.Type IN ('U', 'P', 'V'))

So, I'm hoping someone can give me additional pointers. Thanks!

Answer 1

Did you try to change the default schema to dbo:

ALTER USER [iuser] WITH DEFAULT_SCHEMA=[dbo]
GO

You also can change the owned schema. For example, the user [iuser] owns another schema [abc]:

ALTER AUTHORIZATION ON SCHEMA::[abc] TO [dbo] -- Or [abc] itself
GO

Answer 2

So what do these queries return:

SELECT * FROM sys.objects WHERE name = '___12345679'
SELECT * FROM sys.types WHERE name = '___12345679'

The problem with the queries you post is that they are only looking at some object types.

Answer 3

Hi @CharlieLor ,

Below code returns the full list of objects:

     SELECT OBJECT_NAME(ac.object_id) FROM sys.all_columns ac WHERE name = 'schema_id'  

As ErlandSommarskog said, try：

     SELECT * FROM sys.objects WHERE name = '___12345679'  

You can find objects that depend on the schema: don't look up metadata about your objects.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table?

Answer 4

This select statement shows the object ID. So that means I have to delete the object_id first, correct?

SELECT * FROM sys.objects WHERE name = '___12345679'

Answer 5

You need to find out all objects referenced by the schema "user":

DECLARE  @SchemaName NVARCHAR(100) = 'user';
SELECT * FROM sys.objects WHERE schema_id = SCHEMA_ID(@SchemaName) AND type NOT IN ('S');

And then you need to transfer the objects listed in the output to the other schema or drop them if you do not need them. Finally you are able to drop the schema "user".

If you have XML schema collections, you also need to check:

SELECT * FROM sys.xml_schema_collections WHERE schema_id = SCHEMA_ID(@SchemaName);