Error: The database principal owns a schema in the database, and cannot be dropped

While deleting old SQL server users, I face an error:

“The database principal owns a schema in the database, and cannot be dropped”

While observing we realize the user own schemas, causing this error. To solve this only way is to change schema owner. While fixing this issue I found this nice script on Basit’s blogs.

/************************************************************/

Use 'Database Name'

GO

/*******Declare variable***********/

DECLARE @ID [int] ,

        @CurrentCommand [nvarchar](MAX) ,

        @ErrorMessage   [nvarchar](2000) ,

        @SQLUser        [sysname] , --Specify the name of the database user that you want to drop

        @NewSchemaOwner [sysname];  --Specify the name of the database user that will be used as new schema

                                    --owner for the schemas that is owned by the database user you are dropping

/***********Initialize variable************/

--SET @SQLUser = N'Specify_Database_User_You_Want_To_Drop'; --Example: testuser

--SET @NewSchemaOwner = N'Specify_Database_User_Who_Will_User_As_New_Schema_Owner'; --Example: liveuser

SET @SQLUser = N'domain\usertodelete'; --Example: testuser

SET @NewSchemaOwner = N'domain\userwillownnewschme'; --Example: live

DECLARE @Work_To_Do TABLE

    ( [ID] [int] IDENTITY(1, 1) PRIMARY KEY ,

      [TSQL_Text] [varchar](1024) ,

      [Completed] [bit] );

INSERT  INTO @Work_To_Do  ( [TSQL_Text] ,  [Completed] )

        SELECT  N'ALTER AUTHORIZATION ON SCHEMA::' + [name] + SPACE(1) + 'TO'

                + SPACE(1) + QUOTENAME(@NewSchemaOwner) , 0

        FROM    [sys].[schemas]  WHERE   [principal_id] = USER_ID(@SQLUser);

INSERT  INTO @Work_To_Do  ( [TSQL_Text] , [Completed] )

        SELECT  N'DROP USER' + SPACE(1) + @SQLUser ,  0

SELECT  @ID = MIN([ID])

FROM    @Work_To_Do WHERE   [Completed] = 0;

WHILE @ID IS NOT NULL

    BEGIN

        SELECT  @CurrentCommand = [TSQL_Text]

        FROM    @Work_To_Do WHERE   [ID] = @ID;

        BEGIN TRY

            EXEC [sys].[sp_executesql] @CurrentCommand

            PRINT @CurrentCommand

        END TRY

        BEGIN CATCH

            SET @ErrorMessage = N'"Oops, an error occurred that could not be resolved. For more information, see below:'

                + CHAR(13) + ERROR_MESSAGE()

                        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

             GOTO ChooseNextCommand

        END CATCH

         ChooseNextCommand:

         UPDATE  @Work_To_Do

        SET     [Completed] = 1 WHERE   [ID] = @ID

        SELECT  @ID = MIN([ID])

        FROM    @Work_To_Do WHERE   [Completed] = 0

    END;

/************************************************************/