How do I verify if my login if set to Non-Expiry [SQL Server]?
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.
|
1 2 |
-- 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.
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.