LearnItFirst User Forum

/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This script generates a text file containing all SQL Server authentication logins    except 'sa'.
        It then suggests you load the data from this file on a second     server. This will synchronize logins between two servers so that all
        SQL Server authentication logins have the exact same SIDs.

                Note: if you use this script instead of sp_change_users_login, you won't have the orphaned logins everytime you backup/restore your
                          database on another server/instance.

    Misc Notes: This script should only be run by the most confident of DBAs; this is
        not for the light-hearted! It adds new logins to the target server and, perhaps
        most importantly, stores logins and passwords (encrypted) in a text file.
   
    Versions: SQL Server Only tested on SQL Server 2000 (does not work with SQL Server 2005)
   
    Creation Date: August 28, 2006

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
-- First, get the original logins from the Source server:
EXEC master.dbo.xp_cmdshell
    'bcp "SELECT * FROM master.dbo.sysxlogins WHERE name NOT IN (''sa'',''guest'') AND Name NOT LIKE ''%\%''" queryout c:\logins.txt -T -c -S"Source Server Goes Here"'

-- Log on to the destination server and run this script:
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.xp_cmdshell 'bcp master.dbo.sysxlogins in c:\logins.txt -T -c -S"Destination Server Goes Here"'
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
-- Cleanup the original text file:
EXEC master.dbo.xp_cmdshell 'del c:\logins.txt'