While I do understand the issue you solve here, I have some questions about the strategy in this post.
The merge statement updates every login that has changed, deletes every login that was dropped and inserts every login that was created since the previous inventory, right? So, in short, won't the table hold exactly the same data as the temp table?
Doesn't that remove the need for the merge, and by consequence, for the temp table?
Isn't it easier to truncate and insert the whole result set again?
Also, why are there 3 update statements after the insert to set columns that could be set in one go?
Except for the optional check against the dictionary, wouldn't this be enough:
SELECT @@ServerName ServerName
, a.name AS SQL_Login
, b.sysadmin AS IsSysAdmin
, CAST(LOGINPROPERTY(a.[name], 'PasswordLastSetTime') AS DATETIME) AS PwdLastUpdate
, DATEDIFF(day,CAST(LOGINPROPERTY(a.[name], 'PasswordLastSetTime') AS DATETIME),GETDATE())ASPwdDaysOld
WHEN PWDCOMPARE('', a.password_hash) = 1 THEN 'BLANK PASSWORD'
WHEN PWDCOMPARE(a.name, a.password_hash) = 1 THEN 'Same As Login'
FROM sys.sql_logins a
LEFT JOIN MASTER..syslogins b ON a.sid = b.sid
WHERE a.name NOT LIKE '##%';
Of course, INSERT INTO is equally possible, I'm just trying to work out the flow of getting the data. I think one insert is preferred over an insert and a merge and 3 updates.
Or am I missing something?