Coding Article #: 77 - Published On: June 22, 2017 @ 16:43:35 PM - Last Updated on: January 01, 1900

Supported Files
No Files for this Article.

This script will allow you to change the table Owner to the DBO.
let's say that you download your SQL Server Database from your Hosting Provider, and you import
That database into your SQL Server Management Studio, you will see something similar to this.

T3457654_username.Members
T3457654_username.Profiles

Well, these tables are owned by the SQL Server User, that is yours, on your Hosting Providers SQL Server.
So, you will need to change the owner from "T3457654_username"
To "dbo"

To use the following code.
Browser to the Database, and open a [New Query ]
Next, place the code in the [Query Editor].
Change the "T3457654_username" on line 6, to your information. (Commented on that line)

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

SET @currentOwner = 'T3457654_username' -- Change This to your Information
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