The simplest scenario is to avoid managing individual AD accounts in SQL Server whenever possible. The most common solution is to use AD groups and provide the groups access to the correct SQL server database. Then when people join or leave the AD administrators control what their rights are within AD. This does assume that the AD team has a well defined process for managing accounts.
This was the solution suggested in the comment by @swasheck. I just thought it deserved to be an answer.
The information being requested here is not SQL Server information. It is Windows / OS -level information. This information is not available within SQL Server (i.e. there does not appear to be any DMV containing this info).
It should also be noted that the Login properties of is_policy_checked
and is_expiration_checked
only pertain to SQL Server Logins, not Windows Logins (since Windows Logins already have those enforced as per system / Domain policy).
The only way to get the password policy information is to get it from the Operating System. You can either use a GUI or command-line NET ACCOUNTS
as follows:
C:\>NET ACCOUNTS
Force user logoff how long after time expires?: Never
Minimum password age (days): 0
Maximum password age (days): 42
Minimum password length: 0
Length of password history maintained: None
Lockout threshold: Never
Lockout duration (minutes): 30
Lockout observation window (minutes): 30
Computer role: WORKSTATION
By default, NET ACCOUNTS
displays information for the local system. If your computer is attached to a Domain and your login is a Domain account, then you need to add the /DOMAIN
switch:
C:\>NET ACCOUNTS /DOMAIN
The request will be processed at a domain controller for domain {computers_domain_name}.
Of course, if you don't have permission to see that information, then you need to contact a Domain Administrator.
Best Answer
As far as I know, as long as the users have been created with CHECK_POLICY they will inherit it from the domain.
If not, you'll have to enable the policy use for all of them. Or use SQL Server 2012 BPA, as it has a check for this part (2).
https://msdn.microsoft.com/en-us/library/ms161959(v=sql.110).aspx (1)
https://support.microsoft.com/en-us/help/2028712/understanding-password-policy-for-sql-server-logins (2)