Syncronize Logins?

mikeamikea Posts: 13
edited January 3, 2006 7:54AM in SQL Compare Previous Versions
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

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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?
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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?
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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.
  • Brian,

    Currently, no, it will not script the addlogin.
    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.
  • anybody there? Did Red-Gate just give up on this topic?
  • Mike,

    I've e-mailed you privately about this.
    - Neil Davidson
    Red Gate Software Ltd
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
This discussion has been closed.