SQL Owner Change Script
sp_changeobjectowner [ @objname = ] ‘object’ , [ @newowner = ] ‘owner’
SELECT ‘EXEC sp_changeobjectowner ”’+ SCHEMA_NAME(schema_id) + ‘.’ + OBJECT_NAME(object_Id) + ”’, ”dbo”’FROM sys.tables
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’
In order to generate the Script to Change Owner of all Tables in a database, use this query
SELECT ‘EXEC sp_changeobjectowner ”’+ SCHEMA_NAME(schema_id) + ‘.’ + OBJECT_NAME(object_Id) + ”’, ”dbo”’FROM sys.tables
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’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’
and so on…….
Rock ON!
