What are the challenges you face when working across database platforms? Take the survey
Options

Question on users from SQL Server 2000 to SQL Server 2005

jchasejchase Posts: 5
edited March 31, 2006 7:20AM in SQL Compare Previous Versions
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

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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]
    
  • Options
    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.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    Can you please post up the part of the SQL Compare script that creates the login? Thanks.
Sign In or Register to comment.