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!


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

5 Responses to "Generate Scripts to Change Owner of all Tables in a SQL Server Database"
  1. Anonymous said...
    October 14, 2009 at 6:29 AM

    easy usefull! tnks

  2. Ing. Esteban Castro Rojas said...
    November 26, 2009 at 11:41 AM

    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

  3. Bruce said...
    April 14, 2010 at 2:15 PM

    I love you, man! Thanks for posting this.

  4. Helena said...
    August 23, 2010 at 8:32 PM

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

  5. Anonymous said...
    June 18, 2012 at 4:36 AM

    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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions