Have you ever encountered the error message “The database principal owns a schema in the database, and cannot be dropped” when trying to drop a database user in SQL Server? If so, don’t worry, you’re not alone. This error occurs when the database user you are trying to drop is the owner of one or more database schemas.

Fortunately, there is a simple solution to this problem. In order to successfully drop the database user, you need to transfer the ownership of the schemas to another user. Here’s how you can do it:

Step 1: Identify the User and New Schema Owner

First, you need to identify the name of the database user you want to drop and the name of the database user that will be used as the new schema owner. You can do this by modifying the following two variables in the provided Transact-SQL script:

@SQLUser - Specify the name of the database user that you want to drop
@NewSchemaOwner - Specify the name of the database user that will be used as the new schema owner for the schemas that are owned by the database user you are dropping

Step 2: Execute the Transact-SQL Script

Once you have updated the variables, you can execute the Transact-SQL script. This script will transfer the ownership of all the database schemas associated with the specified database user to the new schema owner, and then drop the database user from the database.

-- Ensure a USE database_name statement has been executed first.
SET NOCOUNT ON;
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 the new schema owner for the schemas that are owned by the database user you are dropping

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

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;

SET NOCOUNT OFF;

Make sure to replace the placeholder values in the script with the actual names of the database users you want to drop and the new schema owner.

By following these steps, you will be able to successfully drop a database user in SQL Server, even if they are the owner of one or more database schemas.

Remember to always exercise caution when making changes to your database and to have a backup in case anything goes wrong.

Happy coding!