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 = 'usesssvc'



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 PreemptionRight;  
GO  

DECLARE @user VARCHAR(100)
DECLARE @pw NVARCHAR(100)
SET @user = 'appSqlFcalculDev'
SET @pw = 'SnsAmy9LRp7uTOWbp565';   


CREATE Login appSqlNameDev WITH PASSWORD = 'xxxxxxx';   
-- Return the SID of the user  
SELECT sid FROM dbo.syslogins  WHERE name = 'appSqlExess'
SELECT sid FROM dbo.sysusers  WHERE name = 'appSqlExess'

CREATE Login appSqlExess WITH PASSWORD = 'xxxxxxx' , SID = 0x0421442FAD357F4B9D269A52B93B78C8;    
CREATE LOGIN appSqlExess WITH PASSWORD = N'xxxxxxx', SID = 0x0421442FAD357F4B9D269A52B93B78C8;


SELECT SID FROM sys.database_principals WHERE name = 'appSqlExess';  
  
-- Change to the second database  
USE rvv;  
GO  
CREATE USER appSqlExess WITH PASSWORD = 'xxxxxxx', SID = 0x0421442FAD357F4B9D269A52B93B78C8;  





ALTER DATABASE PreemptionRight SET TRUSTWORTHY ON 







CREATE USER genSqlTstPreemptionRight WITH PASSWORD = 'xxxxxxxxx';   


genSqlAccIrisNext
genSqlAccIrisNextAdm
genSqlAccIrisNextV2
genSqlAccIrisNextAdmV2




SELECT SID_BINARY(N'S-1-5-21-408552231-458724953-3089381293-513');
-- 0x01050000000000051500000027035A185996571BAD3724B801020000
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;