0

When I run the below query on a linked server it returns nulls in the "DaysUntilExpiration" column as per resultset.

SELECT '<link server>' as Instance,
        SL.name AS LoginName 
      ,LOGINPROPERTY (SL.name, 'PasswordLastSetTime') AS PasswordLastSetTime 
      ,LOGINPROPERTY (SL.name, 'DaysUntilExpiration') AS DaysUntilExpiration 
      ,DATEADD(dd, CONVERT(int, LOGINPROPERTY (SL.name, 'DaysUntilExpiration')) 
                 , CONVERT(datetime, LOGINPROPERTY (SL.name, 'PasswordLastSetTime'))) AS PasswordExpiration 
      ,SL.is_policy_checked AS IsPolicyChecked 
      ,LOGINPROPERTY (SL.name, 'IsExpired') AS IsExpired 
      ,LOGINPROPERTY (SL.name, 'IsMustChange') AS IsMustChange 
      ,LOGINPROPERTY (SL.name, 'IsLocked') AS IsLocked 
      ,LOGINPROPERTY (SL.name, 'LockoutTime') AS LockoutTime 
      ,LOGINPROPERTY (SL.name, 'BadPasswordCount') AS BadPasswordCount 
      ,LOGINPROPERTY (SL.name, 'BadPasswordTime') AS BadPasswordTime 
      ,LOGINPROPERTY (SL.name, 'HistoryLength') AS HistoryLength 
FROM <link server>.[master].sys.sql_logins AS SL 
WHERE is_expiration_checked = 1 
ORDER BY LOGINPROPERTY (SL.name, 'PasswordLastSetTime') DESC

The result set of the above query:

Instance                LoginName                                                                                                                        PasswordLastSetTime                                                                                                                                                                                                                                              DaysUntilExpiration                                                                                                                                                                                                                                              PasswordExpiration      IsPolicyChecked IsExpired                                                                                                                                                                                                                                                        IsMustChange                                                                                                                                                                                                                                                     IsLocked                                                                                                                                                                                                                                                         LockoutTime                                                                                                                                                                                                                                                      BadPasswordCount                                                                                                                                                                                                                                                 BadPasswordTime                                                                                                                                                                                                                                                  HistoryLength

[NLPAGINST11A\NINST11A] dpa                                                                                                                              2018-01-24 08:40:43.970                                                                                                                                                                                                                                          NULL                                                                                                                                                                                                                                                             NULL                    1               0                                                                                                                                                                                                                                                                0                                                                                                                                                                                                                                                                0                                                                                                                                                                                                                                                                1900-01-01 02:00:00.000                                                                                                                                                                                                                                          0                                                                                                                                                                                                                                                                1900-01-01 02:00:00.000                                                                                                                                                                                                                                          0
[NLPAGINST11A\NINST11A] svsql2xdr                                                                                                                        NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                    1               NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL
[NLPAGINST11A\NINST11A] SU_IS_RA                                                                                                                         NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                    1               NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL
[NLPAGINST11A\NINST11A] Obiee                                                                                                                            NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                    1               NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL

The expected result: (The same query, excluding the link server detail, was executed on the server directly)

Instance                LoginName                                                                                                                        PasswordLastSetTime                                                                                                                                                                                                                                              DaysUntilExpiration                                                                                                                                                                                                                                              PasswordExpiration      IsPolicyChecked IsExpired                                                                                                                                                                                                                                                        IsMustChange                                                                                                                                                                                                                                                     IsLocked                                                                                                                                                                                                                                                         LockoutTime                                                                                                                                                                                                                                                      BadPasswordCount                                                                                                                                                                                                                                                 BadPasswordTime                                                                                                                                                                                                                                                  HistoryLength

[NLPAGINST11A\NINST11A] savematrix_user_2                                                                                                                2019-07-08 12:55:22.080                                                                                                                                                                                                                                          80                                                                                                                                                                                                                                                               2019-09-26 12:55:22.080 1               0                                                                                                                                                                                                                                                                0                                                                                                                                                                                                                                                                0                                                                                                                                                                                                                                                                1900-01-01 02:00:00.000                                                                                                                                                                                                                                          0                                                                                                                                                                                                                                                                2019-01-09 11:49:26.837                                                                                                                                                                                                                                          4
[NLPAGINST11A\NINST11A] subscriber_2                                                                                                                     2019-07-08 12:26:36.267                                                                                                                                                                                                                                          80                                                                                                                                                                                                                                                               2019-09-26 12:26:36.267 1               0                                                                                                                                                                                                                                                                0                                                                                                                                                                                                                                                                0                                                                                                                                                                                                                                                                1900-01-01 02:00:00.000                                                                                                                                                                                                                                          0                                                                                                                                                                                                                                                                2019-07-15 14:31:05.207                                                                                                                                                                                                                                          4
[NLPAGINST11A\NINST11A] arcsight                                                                                                                         2019-06-07 13:24:40.667                                                                                                                                                                                                                                          49                                                                                                                                                                                                                                                               2019-07-26 13:24:40.667 1               0                                                                                                                                                                                                                                                                0                                                                                                                                                                                                                                                                0                                                                                                                                                                                                                                                                1900-01-01 02:00:00.000                                                                                                                                                                                                                                          0                                                                                                                                                                                                                                                                2019-06-11 16:00:42.043                                                                                                                                                                                                                                          5

Will it be possible to get the number of days pulled through with query I have or is there another way you could suggest?

Any help, please!

  • I modified the title of your question to reflect what I believe is your desire. You can always roll the edit back if you're not happy with my change. – Scott Hodgin Jul 18 at 15:01
0

I was able to retrieve the correct information by wrapping your original query up in OPENQUERY.

Using OPENQUERY ensures the entire query (including the LOGINPROPERTY stuff) runs on the remote server.

See if this works for you.

SELECT *
FROM openquery(<LinkServer>, 
        '
SELECT ''<LinkServer>'' as Instance,
        SL.name AS LoginName 
      ,LOGINPROPERTY (SL.name, ''PasswordLastSetTime'') AS PasswordLastSetTime 
      ,LOGINPROPERTY (SL.name, ''DaysUntilExpiration'') AS DaysUntilExpiration 
      ,DATEADD(dd, CONVERT(int, LOGINPROPERTY (SL.name, ''DaysUntilExpiration'')) 
                 , CONVERT(datetime, LOGINPROPERTY (SL.name, ''PasswordLastSetTime''))) AS PasswordExpiration 
      ,SL.is_policy_checked AS IsPolicyChecked 
      ,LOGINPROPERTY (SL.name, ''IsExpired'') AS IsExpired 
      ,LOGINPROPERTY (SL.name, ''IsMustChange'') AS IsMustChange 
      ,LOGINPROPERTY (SL.name, ''IsLocked'') AS IsLocked 
      ,LOGINPROPERTY (SL.name, ''LockoutTime'') AS LockoutTime 
      ,LOGINPROPERTY (SL.name, ''BadPasswordCount'') AS BadPasswordCount 
      ,LOGINPROPERTY (SL.name, ''BadPasswordTime'') AS BadPasswordTime 
      ,LOGINPROPERTY (SL.name, ''HistoryLength'') AS HistoryLength
FROM [master].sys.sql_logins AS SL 
WHERE is_expiration_checked = 1 
'
    )
ORDER BY lOGINNAME
  • Tx a mill @Scott Hodgin It is working 100's now!!!! – koosvn069 Jul 28 at 8:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.