Coding Article #: 1089 - Published On: February 13, 2021 @ 04:05:10 AM - Last Updated on: January 01, 1900

Supported Files
No Files for this Article.

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'

DECLARE alterOwnerCursor CURSOR FOR
SELECT [name] FROM dbo.sysobjects 
WHERE xtype = 'U' or xtype = 'P'
AND LEFT([name], 2) <> 'dt'
OPEN alterOwnerCursor
FETCH NEXT FROM alterOwnerCursor INTO @currentObject
WHILE FETCH_STATUS = 0
BEGIN
   SET @qualifiedObject = CAST(@currentOwner as varchar) + '.' + CAST(@currentObject as varchar)
   EXEC sp_changeobjectowner @qualifiedObject, @newOwner
   FETCH NEXT FROM alterOwnerCursor INTO @currentObject
END
CLOSE alterOwnerCursor
DEALLOCATE alterOwnerCursor