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

Okay, before going through all that, my ultimate goal or issue is when I apply a User Mapping from a global security login user "iuser" to a database and I got the following error.

Create failed for User 'iuser'. (Microsoft.SqlServer.Smo)
User, group, or role 'iuser' already exists in the current database. (Microsoft SQL Server, Error: 15023)

The local user "iuser" is already imported along with the database. In the past, I simply delete the 'iuser" from the local database and then re-apply the user mapping from the global login user. However, in this case, I can't because it is tied to a schema and that schema is then tied to an object. If there is a way to resolve my initial issue, I don't need to transfer the objects another schema.

Answer 3

The default schema for the local user "iuser" is "iuser" but also owned another schema and I can't uncheck the schema that it owned.

Answer 4

Okay, I think I got it working. I have to go to the schema that the user 'iuser' owned and change it to dbo. Once that's done, I can now drop the local user 'iuser' and then reapply the mapping from the global security login.

Although the issue in question of this thread is not resolved, the main goal is achieved. Many thanks for all the help!