I'm trying emigrate from SQL Server 2005 to SQL Server 2008. These two database instances are located on third party shared servers that I don't have full permissions to. They're using mixed mode authentication.
I'm running into trouble establishing the brand new database exactly the same way that old one was setup. Particularly, the brand new web-based user interface does not allow a dbo to become specified when designing a brand new database after which when utilizing Red-colored Gate SQL Rival sync in the schemas I'm getting problems because some objects (that do not clearly specify dbo within the script) are now being produced using the prefix from the user account rather dbo.
I've put within the documentation looking for a method to pressure my login "user1" as dbo towards the "db1" database. I found the final outcome the script need to look such as this:
ALTER AUTHORIZATION ON DATABASE::db1 TO user1
Before you run this script, the login "user1" already is available, however is not a person for database "db1". Note I needed to submit this script towards the support of my webhost to be able to run it. Based on the webhost the statement effectively executes, however, if I compare the databases using SQL Compare the consumer "user1" is not physically put into the database enjoy it was under SQL Server 2005. When attemping to include it while using script:
CREATE USER [user1] FOR LOGIN [user1] WITH DEFAULT_SCHEMA=[dbo]
I recieve the mistake message:
"The login already comes with an account within different username"
I've attempted shedding the many other customers in the database, however the error message still continues - that we find odd.
As I could just fix the scripts by clearly indicating dbo, this could inevitably be considered a time explosive device waiting to visit off if any new script were introduced that did not clearly specify dbo there will be a failure throughout synchronization. Since a few of the scripts come from 3rd parties, this isn't a great choice.
So my questions: Can there be another statement that I have to run to be able to add "user1" towards the database like a user that's dbo? Can there be something that has transformed within the implementation between SQL Server 2005 and SQL Server 2008 R2 that may cause these incongruencies?
I'm regrettably unable to test to locate an response to the 2nd one because I do not have another SQL Server 2008 database to check with and that i do not have full use of do anything whatsoever I would like on that one.
That old database was produced via a user interface which forced dbo to become specified and also the brand new one does not allow dbo to become specified when designing a database.
From things i have collected the next should suffice.
ALTER USER [user1] WITH DEFAULT_SCHEMA=[dbo]
The create wasnt being employed as it already been around.