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.
Your local tables are all referenced like:
but once deployed, they are now living in a whole new SQL namespace if you want to use absolute naming:
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?
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?
- We select all the tables in the database that are owned by the user you’ve entered
- 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