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

About: Niraj Kumar


Leave a Reply

Your email address will not be published. Required fields are marked *