Hi narenan ,
1. If CHECK_POLICY=ON in Primary Instance, then StoredProcedure [dbo].[CreateLoginsInSecondary]
script is getting failed on secondary instance, in case of "Alter Login" with below error
Error: a hashed password cannot be set for a login that has check_policy turned on
Solution: Need to add one more condition to check "CHECK_POLICY" while creating Alter command
2. In case of Password change on Primary Instance then StoredProcedure [dbo].[CreateLoginsInSecondary] script is getting failed because of incorrect format of a "ALTER LOGIN" command
Error: Incorrect syntax near 'PASSWORD'. [SQLSTATE 42000] (Error 102) Incorrect syntax near 'PASSWORD'. [SQLSTATE 42000] (Error 102). The step failed.
Solution: Because of substring statement in "ALTER LOGIN" command, either it needs to start from 1 or else need to provide extra space infront of "Password" in below code
Code Block:
N' ALTER LOGIN [' + sp.[name] + N'] WITH DEFAULT_LANGUAGE=[' + ISNULL(l.default_language_name, N'us_english') + N'] '+
Substring(
(
CASE
WHEN CONVERT(NVARCHAR(max), l.password_hash, 2) != CONVERT(NVARCHAR(max), sl.password_hash, 2)
THEN N', PASSWORD=0x' + CONVERT(NVARCHAR(max), l.password_hash, 2) + N' HASHED'
ELSE N' '
END
)