Change Owner of All Tables

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  February 7, 2013 at 7:14 pm

  #269913

  Hello Everyone

  I was wondering if there is a way to change owner of all tables in a database.

  I would like to change from dbo to something like test

  This large company does not have multiple environments for testing, so I am having to use a different database on the Prod box. And I do not want to even take a chance of anything being executed against the prod database. So I have generated a script of all the sprocs, and can easily modify the owner name there from dbo to test.

  I will then drop all the sprocs in the test database and create them using the modified script with <DatabaseName>text.<sproc name>

  This is the kind of thing that can happen when you are a contractor with a large company. They give you only one box.

  Andrew SQLDBA

• Query Shepherd

  SSCrazy

  Points: 2988

  More actions

  February 8, 2013 at 12:38 am

  #1585771

  Suprotim Agarwal

  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:

  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'

  Greetz
  Query Shepherd

• Richard Fryar

  Hall of Fame

  Points: 3457

  More actions

  February 8, 2013 at 6:18 am

  #1585917

  sp_changeobjectowner has been deprecated since 2000 in favour of ALTER SCHEMA and ALTER AUTHORIZATION.

  From your question it looks like you want to change the schema from dbo to test, so the script given by SQL Pizza could be changed as follows:

  SELECT 'ALTER SCHEMA [test] TRANSFER ['

  + SCHEMA_NAME(schema_id) + '].[' + OBJECT_NAME(object_Id) + '];'

  FROM sys.tables

  WHERE SCHEMA_NAME(schema_id) = 'dbo';

  
  Check Your SQL Servers Quickly and Easily
  www.sqlcopilot.com