Fixing inaccessible object owners in a restored SQL Server database
After restoring a backup of a hosted database using SQL Server Authentication I was unable to access all the objects (tables and stored procedures).
I guessed that the problem stemmed from the “User” not appearing under Security as a “Login”. I can create an identically named Login but it appears to remain separate from the User, complaining if I attempt to give it access to the restored database.
This MSDN article contains a stored procedure that should be able to change the object owner on all objects in a database.
Executing the stored procedure runs through all the correct objects without error, but doesn’t change anything.
Reading the fine print confirms that it only prints out the commands that should do the actual work. Copy the output and execute it.
If you execute all the statements printed, expect to see a variety of error messages: Object ‘foo’ does not exit… These appear to be caused by key, index, and trigger ownership change being effected by a prior table ownership change.