In order to change the owner of an object in the current database, use the sp_changeobjectowner system stored procedure. The basic syntax is as follows:
sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'
SELECT 'EXEC sp_changeobjectowner '''
+ SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_Id) + ''', ''dbo'''
Executing the query in SQL Server Management Studio generates the script required to change the owner of all tables in the database. I ran this query on the AdventureWorks database which generated the following output. Right Click on the output and Copy all the records
All you need to do now is paste the script generated and execute it in a query window to change the owner of all tables in the data. The generated script is as shown below:
EXEC sp_changeobjectowner 'Production.ProductInventory', 'dbo'and so on…….
EXEC sp_changeobjectowner 'Sales.SpecialOffer', 'dbo'
EXEC sp_changeobjectowner 'Person.Address', 'dbo'
EXEC sp_changeobjectowner 'Production.ProductListPriceHistory', 'dbo'
EXEC sp_changeobjectowner 'Person.AddressType', 'dbo'
EXEC sp_changeobjectowner 'Sales.SpecialOfferProduct', 'dbo'