SQL SERVER – Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Script to list all the schemas for a database and then construct the code to change it to [dbo].

See here for more information.

USE MyDatabase
go

-- Get the associated schema
SELECT  *
FROM    information_schema.schemata

DECLARE @vcUser VARCHAR(128),
    @vcSQL VARCHAR(8000)
SELECT  @vcUser = 'any non dbo username causing the problem'

IF EXISTS ( SELECT  s.name
            FROM    sys.schemas s
            WHERE   s.principal_id = USER_ID(@vcUser) )

-- Now replace the result name in following script:
    SELECT  @vcSQL = 'ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(s.name) + ' TO dbo;'
    FROM    sys.schemas s
    WHERE   s.principal_id = USER_ID(@vcUser)
            
PRINT @vcSQL
IF 1 = 1
    EXEC (@vcSQL)
    

-- Get the associated schema
SELECT  *
FROM    information_schema.schemata