Copy Guid Sid
to execute on primary and copy and execute to second node
<Declare @user varchar(1000)
Declare @PasswordHash nvarchar(200)
declare @PasswordHashBin varbinary(max)
Declare @sid nvarchar(200)
Declare @sql nvarchar(max)
set @user = 'app_service_login'
SELECT @PasswordHashBin = cast (LOGINPROPERTY(@user,'PASSWORDHASH') AS varbinary (256))
--EXEC sp_hexadecimal @PasswordHashBin, @PasswordHash OUT
--print @PasswordHash
SELECT @PasswordHash= master.dbo.fn_varbintohexstr( cast ( LOGINPROPERTY(@user,'PASSWORDHASH') as varbinary (256)));
select @sid= master.dbo.fn_varbintohexstr(sid) FROM dbo.syslogins WHERE name = @user
set @sql = 'CREATE LOGIN '+ @user +' WITH PASSWORD='+ @PasswordHash +' HASHED , SID = ' + @sid
print @sql
base dev
<USE AppDb;
GO
DECLARE @user VARCHAR(100)
DECLARE @pw NVARCHAR(100)
SET @user = 'app_login_dev'
SET @pw = '<PASSWORD>';
CREATE Login app_login_dev WITH PASSWORD = 'xxxxxxx';
-- Return the SID of the user
SELECT sid FROM dbo.syslogins WHERE name = 'app_login'
SELECT sid FROM dbo.sysusers WHERE name = 'app_login'
CREATE Login app_login WITH PASSWORD = 'xxxxxxx' , SID = 0x00112233445566778899AABBCCDDEEFF;
CREATE LOGIN app_login WITH PASSWORD = N'xxxxxxx', SID = 0x00112233445566778899AABBCCDDEEFF;
SELECT SID FROM sys.database_principals WHERE name = 'app_login';
-- Change to the second database
USE AppDb2;
GO
CREATE USER app_login WITH PASSWORD = 'xxxxxxx', SID = 0x00112233445566778899AABBCCDDEEFF;
ALTER DATABASE AppDb SET TRUSTWORTHY ON
CREATE USER app_user_test WITH PASSWORD = 'xxxxxxxxx';
app_user
app_admin
app_user_v2
app_admin_v2
SELECT SID_BINARY(N'S-1-5-21-1111111111-2222222222-3333333333-513');
-- 0x010500000000000515000000C7353A428E6B748455A1AEC601020000
This function should work across most SID types. The following two queries show it working correctly for Certificates and Asymmetric Keys (you can verify proper translation since these two system catalog views have both forms of the SID in them). And it would work for any Logins created from Certificates and Asymmetric Keys as the SIDs for those (both Logins and Users) are the Cert / Key SIDs:
SELECT [name], [string_sid], [sid], SID_BINARY([string_sid])
FROM [master].sys.certificates;
SELECT [name], [string_sid], [sid], SID_BINARY([string_sid])
FROM [master].sys.asymmetric_keys;