Post reply

sys.sql_logins

  • March 25, 2022 at 6:35 pm

    #4006043

    Hello,

    When I run the following query aginst a "remote" SQL Server, using a Linked Server, all the information is returned EXCEPT DaysUntilExpiration and PasswordExpiration.  They are all NULL.

    When I run the same query "locally" on the SQL Server (of course, not using the Linked Server) all the information is returned INCLUDING DaysUntilExpiration and PasswordExpiration.

    Can someone lte know what I am missing?

    SELECT

    SL.name AS LoginName,

    create_date,

    modify_date

    ,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, 'HistoryLength') AS HistoryLength

    FROM [LinkedServerName].[master].sys.sql_logins AS SL

    --WHERE is_expiration_checked = 1

    ORDER BY LOGINPROPERTY (SL.name, 'LoginName') DESC

    Thanks,

    Terry

    A great day starts with a great attitude

  • March 26, 2022 at 7:10 pm

    #4006449

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • March 26, 2022 at 9:54 pm

    #4006466

    It likely has to do with which login on the target of the linked server was used and what the privs are on the target server for that login.

    For example, I have a Linked Server on my prod server that points to my staging server.  I was relieved (confirms that I did a good job security wise) to see that only two logins were returned from the linked server and all of the logins were return from the staging server when I logged into the staging server to run the code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 28, 2022 at 3:53 pm

    #4007152

    The DaysUntilExpiration argument will return NULL if the setting enforce password policy or expiration are not checked.

    You may want to add a check to see if these are checked, and then adjust your code accordingly

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • March 28, 2022 at 4:06 pm

    #4007157

    Hello,

    Further research has confirmed that the PasswordExpiration and the DaysUntilExpiration fields will only be returned if the linked server is called from the openquery statement.

    SELECT *

    FROM openquery([LinkedServerName],

    Thanks,

    Terry

    A great day starts with a great attitude

  • March 28, 2022 at 4:08 pm

    #4007158

    Like this ...

    SELECT *

    FROM openquery([LinkedServerName],

    '

    SELECT ''<[LinkedServerName>'' 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 DaysUntilExpiration DESC

    A great day starts with a great attitude

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply