Previous Article
SQL Server Management Studio - How to alter the default value or binding of a table column
SQL Server Management Studio - Change Table Owner
Go Back To List
SQL (Structured Query Language)
Coding Article #: 1089 - Published On: February 13, 2021 @ 04:05:10 AM - Last Updated on: January 01, 1900
This article has been Favorited 0 times
Join today, and add this to your favorites.
Coding Source - Share on MySpace Coding Source - Share With Facebook Coding Source - Share on Twitter Coding Source - Share on Reddit Coding Source - Share on Digg It Coding Source - Share on Stumble Upon It Coding Source - Share on Delicious
Share With Friends (Updated 6-8-2010)

Supported Files
No Files for this Article.
No Screenshot Available
In this script, we are going to change the table owner from the old owner to the default DBO owner.
This usually needs to be done when you are moving your database from a hosting company to either another hosting company or, to your own server.

Open SQL Server Management Studio
Right-Click on the database and choose [New Query]
Next, paste the following code and edit the [currentOwner] and the [newOwner] value. (New Owner is usually dbo)
Next, click the [Ecxecute] button

DECLARE @currentObject nvarchar(517)
DECLARE @qualifiedObject nvarchar(517)
DECLARE @currentOwner varchar(50)
DECLARE @newOwner varchar(50)

SET @currentOwner = 'Old_Owner_Name'
SET @newOwner = 'dbo'

SELECT [name] FROM dbo.sysobjects 
WHERE xtype = 'U' or xtype = 'P'
AND LEFT([name], 2) <> 'dt'
OPEN alterOwnerCursor
FETCH NEXT FROM alterOwnerCursor INTO @currentObject
   SET @qualifiedObject = CAST(@currentOwner as varchar) + '.' + CAST(@currentObject as varchar)
   EXEC sp_changeobjectowner @qualifiedObject, @newOwner
   FETCH NEXT FROM alterOwnerCursor INTO @currentObject
CLOSE alterOwnerCursor
DEALLOCATE alterOwnerCursor
Post to Facebook about: SQL Server Management Studio - Change Table Owner