Here’s a script I’m using to synch logins (SQL and Windows) between 2 SQL instances.  I got the first version of the script from Tara Kizer and modified it a bit to handle a few different cases.  The script is a SQLCMD script that expects a parameter called $(PRIMARY), the value of which must exist as a linked server on the instance it runs from.

The original purpose of the script was to keep passwords in In my case one instance is PROD and the other is DR, so I have the script set up to run on both boxes but the job is disabled on the PROD box.  I have links on both instances set up to use current login context which works in most cases but is susceptible to double hop issues since as a NON domain admin I can’t make my own SPN entries and can’t be bothered to request them.

Code is pasted below but here is a link to the script as well just in case the formatting sucks, and it probably will.

— sqlcmd -S .\DRINSTANCE -E -iDRSyncLogins.sql -oDRSyncLogins.log -v PRIMARY=”PRODINSTANCE”
–:setvar PRIMARY “.\DRINSTANCE”

SET NOCOUNT ON

USE [master]
GO

CREATE TABLE #logins
(
loginId int IDENTITY(1, 1) NOT NULL,
loginName nvarchar(128) NOT NULL,
passwordHash varbinary(256) NULL,
[sid] varbinary(85) NOT NULL,
[type] varchar (1)
)

— openquery is used so that loginproperty function runs on the remote server, otherwise we get back null
INSERT INTO #logins(loginName, passwordHash, [sid], [type])
SELECT *
FROM OPENQUERY([$(PRIMARY)], ‘
SELECT
[name]
,CONVERT(varbinary(256), LOGINPROPERTY(name, ”PasswordHash”))
,[sid]
,case type_desc
when ”SQL_LOGIN” then ”S”
when ”WINDOWS_GROUP” then ”W”
when ”WINDOWS_LOGIN” then ”W”
end
FROM
master.sys.server_principals
where
type_desc IN ( ”SQL_LOGIN”, ”WINDOWS_LOGIN”, ”WINDOWS_GROUP” )
and
name NOT IN (”sa”, ”guest”)
and
[name] not like ”sa%”
ORDER BY name’)

–select * from #logins

DECLARE
@count int
,@loginId int
,@loginName nvarchar(128)
,@passwordHashOld varbinary(256)
,@passwordHashNew varbinary(256)
,@sid varbinary(85)
,@sql nvarchar(4000)
,@password varchar(514)
,@sidplain varchar(514)
,@type varchar(1)

SELECT
@loginId = 1
,@count = COUNT(*)%0