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

or we can use the query bellow :

SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + o.Name
FROM sys.Objects o
INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
WHERE s.Name = ‘yourschema’
And (o.Type = ‘U’ Or o.Type = ‘P’ Or o.Type = ‘V’)

copy all the result of the query above, then paste it on query SQL Server editor and run it

then the shcema and the owner of the table will change to the schema that we want.

Advertisements

About alamzyah
Name : Alamsyah Nick Name : Alamzyah Place of Birth : Jakarta, 04 June 1983 sex : Male Religion : Moslem Region : Jakarta, Indonesia Specialist : IT, Computer mail : alamzyah@gmail.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: