locked
Sync SQL Logins & PWs between 2 different SQL Servers & versions? RRS feed

  • Question

  • Hello is there a way to create a new Login & PW on a SQL 2008 server and copy the same credentials to another SQL 2014 server via TSQL script?

    Am aware of Microsoft's script which mentions it also copies the SID which I believe I would not rather since we already have a bunch of existing Logins on destination server and that it might have same SID with different login name?

    Thanks in advance.

    Thursday, June 22, 2017 5:01 PM

All replies

  • Hello,

    The SID is a varbinary(85) value (see sys.server_principals (Transact-SQL)), such a very, very high value generated by random, that's really impossible the different logins gets equal values; so go on use the sp_help_revlogin to create login scripts (source How to transfer logins and passwords between instances of SQL Server)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, June 22, 2017 5:18 PM
  • The sids that are generated by SQL logins can be transferred to another server. The sids for windows logins correspond to the SID in the active directory.

    Robert Davies has a script that will allow you to automatically transfer the logins as they are created.

    http://sqlsoldier.net/wp/sqlserver/transferring-logins-to-a-database-mirror

    Thursday, June 22, 2017 5:27 PM
  • Thanks for your speedy reply, much appreciated Hilary.

    What if the Login name exists on both local SQL Server and another remote SQL Server and the user want to change their own PW one of the SQL Servers...what would be a method to also change the new PW same to the other SQL Server including consistency...so if PW changed failed on first server then it would not proceed to second server and send like an email notifying change PW process failed?  (Believe some ALTER LOGIN mechanism would be used.)

    And vice versa if it succeeds on first server but fails on second server first server PW change can be rolled back to old PW?

    Thursday, June 22, 2017 8:58 PM
  • Contained database are designed to fit this requirement, but are intended for Always on.

    I have something at a client site where I hack into the eventing subsystem to do this. I'll post it tonight.

    Thursday, June 22, 2017 9:00 PM
  • So there really is no way to rollback overall password changes on both servers if one password change fails on one of the servers?

    Thanks.

    Wednesday, July 12, 2017 9:59 PM