--
-- 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:
Labels: SQL Server, SQL Server Administration, T-SQL Tips