Check password against policy

Last Post 08 Jul 2007 11:49 AM by rsavage. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

03 Jan 2007 07:41 AM
Is there a means to check a password to see if it meets the password policy?

Even though I am a member of the SysAdmin, I do not necessarily create / manage the SQL logins on our servers. Nor I do not have access to many SQL server login passwords to check whether thy comply with our password policy.

Knowing that SQL 2005 has the posiibility of checking the passwords against the policy if the option is turned on, I tried scripting logins and password (see MSKB 246133 which I modified for SQL2005) from a SQL 2000 box to run the create login scripts on a SQL 2005 box with the CHECK POLICY = ON option.

It could not analyse the password because of the way the script is built.

I was looking for a way to write a simple T-SQL script to help me gererate a report and present it to developers (who control the passwords) to show them those accounts that do not meet the policy.

For example, I was hopijng that this script would ck<heck whether the existing password met the password policy:

alter login TestLogin with CHECK_POLICY = ON

Is there a way to make this possible?
New Member
New Member

20 Feb 2007 09:23 AM
Hi There,

As you know SQL server logins will get the policy from the group policies of your domain. If your domain policy enforces password expiration for accounts, you can configure your existing logins to have the password policy on.

This will force the sql logins to change the password after a certain period of time and ensure all the new passwords comply with the domain group policies.



New Member
New Member

20 Feb 2007 09:57 AM
Totaly agree with rm. the password check will only be enforced when logins change their passwords the next time.

When CHECK_POLICY is changed to ON, the following behaviors occur:
1. CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF.
2. The password history is initialized with the value of the current password hash.

New Member
New Member

08 Jul 2007 11:49 AM
Hi Desperado:

I don't think anyone understood what you were looking for. Try this:

SELECT * FROM sys.sql_logins

This will show you for each account whether "Enforce password policy" (is_policy_checked field) and "Enforce password expiration" (is_expiration_checked field) are checked.

Good luck.

Acceptable Use Policy