Delete SQL Server logins on replicated server

Question

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).

Answer 1

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)

Answer 2

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