MSSQL: Transferring ownership from one owner to another in sql server.

Occasionally, this could happen in vCenter where some objects belong to other owners aside from dbo.

Referemce: https://msdn.microsoft.com/en-us/library/ms173423.aspx

Transfer vpx_version from dbo to guest.

alter schema guest transfer dbo.vpx_version;

sp_tables vpx_version

select distinct sys.schemas.name from sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id and sys.schemas.name <>'sys';

Transfer vpx_version from guest to dbo.

alter schema dbo transfer guest.vpx_version;

sp_tables vpx_version

To change all objects from none dbo to dbo schema.

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o. Name

    FROM sys.Objects o

    INNER JOIN sys .Schemas s on o. schema_id = s. schema_id

    WHERE s.Name = 'dbo'

    And (o. Type = 'U' Or o .Type = 'P' Or o.Type = 'V')