Question on users from SQL Server 2000 to SQL Server 2005

jchase

We currently have SQL Server 2000 on all of our clients and most of them are requesting 2005 - so for the schema upgrade I am getting the following errors (Warnings tab) on the Synchronzie ...

The user Administrator does not have an associated login. If the user was not defined with WITHNOLOGIN then the source database is in an inconsistent state. Please fix this by using sp_change_users_login.
The user Administrator does not have an associated login. The target database is in an inconsistent state. Please fix this by using sp_change_users_login.
The user Cruncher does not have an associated login. The target database is in an inconsistent state. Please fix this by using sp_change_users_login.


Does SQL Compare not script the appropriate changes for SQL Server 2005 for the users ? What exactly needs to be done for this to work 100%?

Thanks,
Jon

Brian Donahue

Hello Jon,

If the synchronization script was built as a result of comparing SQL 2000 to SQL 2000, then I understand this wouldn't work. You should need to compare the SQL 2000 to the SQL 2005 to get the correct database access scripted. By default, new users aren't even granted CONNECT rights to a server. SQL Compare knows this, but only if you had actually generated the script against a SQL Server 2005.

If this is impractical, you would need to save the script and make sure you script something like this:

IF NOT EXISTS (SELECT * from sysusers where name='@newusername') BEGIN
CREATE USER [@newusername] FOR LOGIN [@newusername] WITH DEFAULT_SCHEMA=[dbo]'
GRANT CONNECT TO [@newusername]

jchase

This comparison was from a 2000 to a 2005 db and those errors were the end result. And from figuring out the solution - as the solution that you have provided would not give you permission to the DB once you log into the server. You would need to assign the user to a server role member that would have the proper access defined.

Brian Donahue

Hello,

Can you please post up the part of the SQL Compare script that creates the login? Thanks.