title: Make a login into dbo for a database in SQL Server
body: <p>I am attempting to migrate from SQL Server 2005 to SQL Server 2008. Both of these database instances are hosted on 3rd party shared servers that I do not have full permissions to. They are using mixed mode authentication.</p>
  
  <p>I am running into trouble setting up the new database the same way the old one was set up. Specifically, the new web-based control panel doesn't allow a dbo to be specified when creating a new database and then when using Red Gate SQL Compare to sync up the schemas I am having problems because some objects (that don't explicitly specify dbo in the script) are being created with the prefix of the user account rather dbo.</p>
  
  <p>I have poured over the documentation trying to find a way to force my login "user1" as dbo to the "db1" database. I came to the conclusion that the script should look like this:</p>
  
  <pre><code>ALTER AUTHORIZATION ON DATABASE::db1 TO user1
  </code></pre>
  
  <p>Before running this script, the login "user1" already exists, but is not a user for database "db1". Note I had to submit this script to the support of my hosting company in order to run it. According to the hosting company the statement successfully executes, however when I compare the databases using SQL Compare the user "user1" has not been physically added to the database like it was under SQL Server 2005. When trying to add it using the script:</p>
  
  <pre><code>CREATE USER [user1] FOR LOGIN [user1] WITH DEFAULT_SCHEMA=[dbo]
  </code></pre>
  
  <p>I get the error message:</p>
  
  <pre><code>"The login already has an account under a different username"
  </code></pre>
  
  <p>I have tried dropping all of the other users from the database, but the error message still persists - which I find odd.</p>
  
  <p>While I could just fix the scripts by explicitly specifying dbo, this would inevitably be a time bomb waiting to go off because if any new script were introduced that didn't explicitly specify dbo there would be a failure during synchronization. Since some of the scripts are from 3rd parties, this is not a good solution.</p>
  
  <p>So my questions: Is there another statement that I need to run in order to add "user1" to the database as a user that is dbo? Is there anything that has changed in the implementation between SQL Server 2005 and SQL Server 2008 R2 that could cause these inconsistencies?</p>
  
  <p>I am unfortunately not able to test to find an answer to the second one because I don't have another SQL Server 2008 database to test with and I don't have full access to do anything I want on this one. </p>
  
  <p>The old database was created through a control panel which forced dbo to be specified and the new one doesn't allow dbo to be specified when creating a database.</p>