When it comes to logins to SQL Server, there are basically 3 types:

• SQL Server-based logins
• Windows user accounts
• Windows security groups

The latter two we have limited control. We can grant them the ability to connect to SQL Server and we can then make them users within the databases. But with respect to passwords and password expiration/strength, there's nothing we can do within SQL Server. Those accounts (or the accounts which are members of the group, in the case of a Windows group login) are controlled either by the operating system (an account local to the computer) or by Active Directory (domain account). So other than auditing for their existence on a SQL Server and their fixed server role memberships, there's not a whole lot more we do with respect to them at the server level. SQL Server-based logins are a different story.

With SQL Server-based logins, we can (as of SQL Server 2005) control whether or not they adhere to the password policies set at the operating system level (and which the computer may receive from the domain via a group policy). We can choose whether or not password complexity and lockout is enforced as well as whether or not password expiration is checked. In SQL Server 2005 and above, the key to audit this information is to use the catalog view sys.sql_logins. The reason for using this over sys.server_principals is that it only returns information on the SQL Server-based logins and it returns all the information we would want to check related to a SQL Server-based login. It also inherits all the columns from sys.server_principals, meaning we don't have to reference that catalog view as well. 

Here are several simple queries that we can use to audit our SQL Server environment:

SQL Server Logins which are currently disabled:

SELECT name 
FROM [sys].[sql_logins]
WHERE [is_disabled] = 1;   

SQL Server Logins which do not adhere to the password policy:

SELECT name 
FROM [sys].[sql_logins]
WHERE [is_policy_checked] = 0;  

SQL Server Logins which do not adhere to password expiration:

A note about this one. Password expiration is only checked if the password policy is enforced. If the password policy is not enforced, then SQL Server won't check for password expiration, meaning that SQL Server login effectively has a password that never expires. Therefore, we need to check both when there is no password policy enforcement as well as when there is password policy enforcement, but no password expiration enforcement.

SELECT name 
FROM [sys].[sql_logins]
WHERE [is_policy_checked] = 0
   OR  ([is_policy_checked] = 1 AND [is_expiration_checked] = 0);