Microsoft
MicrosoftshruthigrI still get the same error as the guy above: Incorrect syntax near 'PASSWORD'. [SQLSTATE 42000] (Error 102). The step failed. I added the extra space in front of PASSWORD but the code still doesn't work
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
)
I just figured out the issue, the problem is the Substring you have on line 2 above. I think the original code didn't have the "WITH DEFAULT_LANGUAGE" in it as a permanent statement. In the original logic, all of the modifications were done in the conditional statements that follow which means there would always be an extra comma before the first statement. The substring removed that extra comma. However, at some point the default language was added as a permanent addition to the alter statement which means that the substring is now always removing a needed comma, and it just happens that PASSWORD is the first statement to be appended which it is the one now missing the comma.
Remove the substring and then it will work.
