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

Synchronize Logins.

mikeamikea Posts: 13
edited December 2, 2005 7:31AM in SQL Compare Previous Versions
Re: Topic: http://www.red-gate.com/MessageBoard/viewtopic.php?t=1390

OK. This still does generate the logins, but now I at least see the warning below about it. I guess I will just have to enter them by hand, but I was hoping that I didn't have to because there a about a hundred of them that I need to set up on my test SQL Server.

The user User1 does not have an associated login. The target database is in an inconsitent state. Please fix this by using sp_change_users_login.
The user User2 does not have an associated login. The target database is in an inconsitent state. Please fix this by using sp_change_users_login.

Comments

  • Options
    mikea wrote:
    Re: Topic: http://www.red-gate.com/MessageBoard/viewtopic.php?t=1390

    OK. This still does generate the logins, but now I at least see the warning below about it. I guess I will just have to enter them by hand, but I was hoping that I didn't have to because there a about a hundred of them that I need to set up on my test SQL Server.

    The user User1 does not have an associated login. The target database is in an inconsitent state. Please fix this by using sp_change_users_login.
    The user User2 does not have an associated login. The target database is in an inconsitent state. Please fix this by using sp_change_users_login.

    Hi Mike,

    Logins are more server level than database level. There are many problems with migrating logins automatically (especially if they are Windows login based, or certificate/asymmetric key based in the case of SQL Server 2005). It would not be a good idea if SQL Compare started to create NT users (in which case deciding on the default domain would also cause a problem). Since it is very difficult to change a user later (if one wants to change a SQL user to a certificate based user, he would need to unbind all the relevant dependent objects), and since logins affect the whole database server, we advise their manual migration.

    The above warning indicates that you probably have restored a foreign backup onto a new server. The master db is a bit messed up in such cases, but this one can correct either manually or by writing a short proc that iterates through the database users, checks whether their sid is included in the master syslogins table, and runs the sp_change_users_login.

    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
  • Options
    But it looks like you already generate the code. I used what you generated (The sp_grantlogin), then wrote a script that filled in the user name, then it seemed to work for SQL 2000.

    cap.jpg
  • Options
    I also have problem with users if script failed later (say in creating views ).

    The problem is:
    SQL Compare rolled back all script, BUT User (or role) are not rollback.
    They stey created in database.

    When I exclude some object, I can't sinchronize database, becuse, script thinks that they doesn't exists.

    If I have users or roles in script, I have Refresh comparation before sinhronize them again
  • Options
    rdobrich wrote:
    I also have problem with users if script failed later (say in creating views ).

    The problem is:
    SQL Compare rolled back all script, BUT User (or role) are not rollback.
    They stey created in database.

    When I exclude some object, I can't sinchronize database, becuse, script thinks that they doesn't exists.

    If I have users or roles in script, I have Refresh comparation before sinhronize them again

    Unfortunately user creation, together with role create/drops, role membership changes, ... are not transactional, and thus we have to handle them outside a transaction. One would think that SQL Server 2005 fixed this issue with the new CREATE ROLE, etc, but they have not (sp_addrolemember) is still nontransactional and thus the above behaviour will remain in SQL Server 2005 too. (I've been promised that it will be fixed in SP1, but that is still away).

    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
  • Options
    mikea wrote:
    But it looks like you already generate the code. I used what you generated (The sp_grantlogin), then wrote a script that filled in the user name, then it seemed to work for SQL 2000.

    I'll contact you in a private message about this.

    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
This discussion has been closed.