One of the niggling that often happens when your deployed to a cheap shared hosting environment is having your table owners change on you. If you have created a data access layer that references tables using their long name this is an issue. This can be caused by a deployment script not maintaining user permissions on your tables or maybe you’re using a web console to interract with you database like myLittleAdmin and it doesn’t allow you to create a table under any user but your own.

However you’ve come to this issue, one common theme runs through them all: They can be a definite frustration. Deploying at haste can make these issues even more prevalent if you have several hundred tables.

The Problem

Your local tables are all referenced like:

[dbo].[tblMyImportantTable]

but once deployed, they are now living in a whole new SQL namespace if you want to use absolute naming:

[crappySharedHostUser1234].[tblMyImportantTable]

While there are a number of ways to get around this including rewriting your data layer to simply reference the table name, sometimes this is not an option (especially if your organisation has strict Q&A on all check ins.

So the solution is to bulk update all your tables to use the DBO user. This is generally a recommended way to run anyway, so why change things?

The Solution

This little sql script gem will have you on your way in no time. Simply change the database user to that of the tables that have been created (in my example above i am referring to the user crappySharedHostUser1234).

DECLARE myCursor CURSOR
FOR
    SELECT 'ReplaceWithUserToFindReplace.' + [name]
        FROM sysobjects
        WHERE xtype = 'u'
OPEN myCursor
    DECLARE @tableName NVARCHAR(517)
    FETCH NEXT FROM myCursor INTO @tableName

    WHILE @@fetch_status = 0
    BEGIN

        EXEC sp_changeobjectowner @tableName, 'dbo'

        FETCH NEXT FROM myCursor INTO @tableName
    END
CLOSE myCursor
DEALLOCATE myCursor

What are we doing here?

1. We select all the tables in the database that are owned by the user you’ve entered
2. We then cycle through them all and run the built-in stored procedure sp_changeobjectowner to change the table owner to dbo

Simple, and easy