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;