GENERATE SCRIPT TO TRANSFER SCHEMA OWNERSHIP
SELECT ‘ALTER SCHEMA NewSchemaName TRANSFER [‘ + SysSchemas.Name + ‘].[‘ + DbObjects.Name + ‘];’
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = ‘OldSchemaName’
AND (DbObjects.Type IN (‘U’, ‘P’, ‘V’))
————————————————-
—–Change Schema owner and delete user one by one
————————————————-
USE [DATABASE NAME]
GO
ALTER AUTHORIZATION ON SCHEMA::[Other Schema] TO [dbo]
GO
USE [DATABASE NAME]
GO
/****** Object: User [Domain\Login] ******/
——–DROP USER [Domain\Login]
——–GO