—
— Retrieves Security-Related Information
— for all the SQL Server Logins
—
— SQL Server versions supported: SQL Server 2005 or later
—
SELECT
[name] as LoginName,
LOGINPROPERTY ([name] , ‘DefaultDatabase’) as DefaultDatabase,
LOGINPROPERTY ([name] , ‘DaysUntilExpiration’) as DaysUntilExpiration,
(CASE ISNULL(LOGINPROPERTY ([name] , ‘IsExpired’),0) WHEN 0 THEN ‘False’ ELSE ‘True’ END) as IsExpired,
(CASE ISNULL(LOGINPROPERTY ([name] , ‘IsLocked’),0) WHEN 0 THEN ‘False’ ELSE ‘True’ END) as IsLocked,
LOGINPROPERTY ([name] , ‘PasswordLastSetTime’) as PasswordLastSetTime,
LOGINPROPERTY ([name] , ‘PasswordHashAlgorithm’) as PasswordHashAlgorithm
FROM master..syslogins
— Retrieves Security-Related Information
— for all the SQL Server Logins
—
— SQL Server versions supported: SQL Server 2005 or later
—
SELECT
[name] as LoginName,
LOGINPROPERTY ([name] , ‘DefaultDatabase’) as DefaultDatabase,
LOGINPROPERTY ([name] , ‘DaysUntilExpiration’) as DaysUntilExpiration,
(CASE ISNULL(LOGINPROPERTY ([name] , ‘IsExpired’),0) WHEN 0 THEN ‘False’ ELSE ‘True’ END) as IsExpired,
(CASE ISNULL(LOGINPROPERTY ([name] , ‘IsLocked’),0) WHEN 0 THEN ‘False’ ELSE ‘True’ END) as IsLocked,
LOGINPROPERTY ([name] , ‘PasswordLastSetTime’) as PasswordLastSetTime,
LOGINPROPERTY ([name] , ‘PasswordHashAlgorithm’) as PasswordHashAlgorithm
FROM master..syslogins
For more info, check out the following links:
Artemakis Artemiou is a seasoned Senior Database and AI/Automation Architect with over 20 years of expertise in the IT industry. As a Certified Database, Cloud, and AI professional, he has been recognized as a thought leader, earning the prestigious Microsoft Data Platform MVP title for nine consecutive years (2009-2018). Driven by a passion for simplifying complex topics, Artemakis shares his expertise through articles, online courses, and speaking engagements. He empowers professionals around the globe to excel in Databases, Cloud, AI, Automation, and Software Development. Committed to innovation and education, Artemakis strives to make technology accessible and impactful for everyone.