Syncronize Logins?
mikea
Posts: 13
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
How can I get SQL Compare to move my "logins" not the "USERS" to the development server?
Thanks,
Mike
This discussion has been closed.
Comments
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?
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
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?
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).
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.
Currently, no, it will not script the addlogin.
I've e-mailed you privately about this.
Red Gate Software Ltd