Syncronize Logins?

mikea

I compared 2 servers and only check the logins that I want to move, clicked generate script and the data is not there. I checked for an update in the product, however it states that I have the most up to date product.

How can I get SQL Compare to move my "logins" not the "USERS" to the development server?

Thanks,
Mike

Brian Donahue

Hello Mike,

Sorry, I'm a bit confused. SQL Compare allows you to compare database users. The user's login is considered an attribute of the user. So if you select a user to be migrated to another server, SQL Compare will script an sp_addlogin and an sp_grantdbaccess to add the login and then grant them access to the database.

Is it that you just want to move the logins without granting database access?

mikea

I have two servers. Production and development. I restored a DB from Production to development. So, all the users in the database are there, but, the security Logins are not, so I wanted to create a script to create all the security logins on the development server.

To accomplish this, I performed a compare between the two databases, it listed several changes because of replication and users. I unchecked everything, and selected a few users. Then I clicked "Syncronize" then I selected Make Local like production, then I clicked next, then looked at the script and is was basically blank. here it is:

/*
Script created by SQL Compare from Red Gate Software Ltd at 11/21/2005 11:32:59 AM
Run this script on (local).SALES to make it the same as PRODSERVER.SALES
Please back up your database before running this script
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
@TRANCOUNT>0 BEGIN
PRINT 'The database updated succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

Brian Donahue

Hello,

That's a bit more clear, thanks! I can't seem to be able to reproduce this on the latest version (3.2.1.14). If I have database users that are the same, the latest version doesn't try to re-create the login like I think it used to.

But I would be curious to know why it is pointing the users out as a difference and then not scripting them! Are these Windows logins/users? And you'd restored the database to another machine, so the SIDs of the logins don't match?

mikea

I guess it senses that the "login" is not there, but it wont script it because the database "user" is there.

The Login's can be found from Enterprise Manager, then Security, then Logins.

I checked my version and I am also running (3.2.1.14).

Brian Donahue

Hi Mike,

Yes, SQL Compare wants to compare users but it can't add a user to a database unless the login exists first; that's why it scripts an addlogin.

mikea

Brian,

Currently, no, it will not script the addlogin.

Brian Donahue wrote:

Hi Mike,

Yes, SQL Compare wants to compare users but it can't add a user to a database unless the login exists first; that's why it scripts an addlogin.

mikea

anybody there? Did Red-Gate just give up on this topic?

Neil Davidson

Mike,

I've e-mailed you privately about this.

Brian Donahue

Version 4 of SQL Compare will allow you to toggle the comparison of differences between database user properties and SQL Server login properties for SQL Server authentication accounts. I believe this will help with this particular issue.