July 06, 2009

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

image

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, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

5 comments:

Anonymous said...

easy usefull! tnks

Ing. Esteban Castro Rojas said...

Great, thanks for the tip. I changed it so it can work on SQL Server 2008:

SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) +
'.' + OBJECT_NAME(object_Id) + ';'
FROM sys.tables

Bruce said...

I love you, man! Thanks for posting this.

Helena said...

This is spot on - solved my DB problem at work. Thanks very much!

Anonymous said...

Thanks Alot!!

Which one to use for sql 2008 64 Bit,

SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) +
'.' + OBJECT_NAME(object_Id) + ';'
FROM sys.tables

or

SELECT 'EXEC sp_changeobjectowner '''+ SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_Id) + ''', ''dbo'''FROM sys.tables