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:
Write commentsPlease do not enter spam links