Today I was working on multiple access/permission requests of same user on multiple servers. Due to previous poor experience using sql authenticated login set to expiry by DBA, he kept asking me to verify if the logins are set to Non-Expiry. So even though I was sure enough of setting the newly created sql authenticated logins to non-expiry, I had to devise a method to answer the user’s question.
Resolution:- Answer to this question is quite easy. Other than following GUI method, one can use below query to verify if the session login is set to non-expiry or not.
-- Check the Password Expiry date for session login
select SUSER_NAME() as LoginName, LOGINPROPERTY(SUSER_NAME(),'DaysUntilExpiration') as Days2Expire
For more, check this MSDN url.
The query will return NULL value if the SQL login password is set to Non-Expiry, otherwise number of days will be returned. It’s that easy. Happy Coding 🙂
Please feel free to correct me by comments. If you liked the article, do Like, Share & Comment.