Generate Scripts to Change Owner of all Tables in a SQL Server Database

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…….

Kewl!

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook