Synchronize Logins.

mikea

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.

Andras B

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

mikea

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.

rdobrich

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

Andras B

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

Andras B

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