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

Read more of this post

Advertisements