We have a production database which is replicated on another server at a remote location. The local database users (not logins) are also replicated in this database. However, the SQL logins are not replicated from production to the remote server.

What is the best way to replicate "logins" to the remote server with passwords and all?

Here's my approach and I need to know if I'm going the right direction.

  1. Need help with a script that will drop all logins on the replicated server.
  2. EXEC [production server instance].[master].[dbo].[sp_help_revlogin] from the replicated server to get an accurate production list of logins.
  3. Find a way to actually execute the results from sp_help_revlogin that will script out the logins on the replicated server with hash passwords, SIDs etc.

Any major problems with my approach? If not, I could use some help actually writing the scripts or outline the exact process for 1-3 (made some attempts but have had problems getting the scripts to work).

  • Why do you need part 1? Why'd you remove already existing logins from the replicated server? Is the server in the same domain? Are there any applications that are using using the replication server (reporting)? PS: it's normal that the server logins aren't replicated, as replication moves only database objects. You need to prepare the server beforehand. I think that your process looks alright as is, but test it properly on some test machines before.
    – Marian
    May 24 '13 at 8:36

I dont see a need for 1.

You can use below script to move logins from one server to another. It uses SQLCMD and xp_cmdshell.

You can also look for a PowerShell option if you dont want to use xp_cmdshell.

set nocount on
--  Author  :: Kin
--  Desc    :: Move Logins from one server to another
-- Version  :: 1.0 for dba.stackexchange.com
--  Date    :: 05/24/2013
-- Change your paths for the output from D:\logs\ to whatever suits your need

declare     @dbid tinyint,
            @SQLText varchar(8000),
            @destserver varchar(255),

set @destserver ='' --Insert your Destination Server NAme in here.
--1.Transfer Logins
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@@servername+' -E -Q"execute master.dbo.sp_help_revlogin" -oD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)
-- Create on Destination Server.
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@destserver+' -E -iD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)

Not sure if this is what you're after but when I migrate databases I generate the CREATE LOGIN statements like this

select 'create login ' + name + ' with password=', password_hash, ' hashed, sid=', sid from sys.sql_logins

Note that this transfers the SQL logins only

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy