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;
/************************************************************/


No comments:

Post a Comment