How to List all SQL Logins with Passwords not Enforced by Policies

To list all SQL logins whose passwords are not enforced by policies in SQL Server, use the following query:

SELECT name, is_disabled, is_policy_checked
FROM sys.sql_logins
WHERE is_policy_checked = 0
ORDER BY name;

logins_password_expiration

To enforce password policy for a login, use the following query:

ALTER LOGIN Login_name WITH CHECK_POLICY = ON

To enforce password policy for a login and password expiration, use the following query:

ALTER LOGIN Login_name WITH CHECK_POLICY = ON,
CHECK_EXPIRATION = ON;

To enforce password policy for a login, password expiration and to force the login to change the password at next login (you must provide a password), use the following query:

ALTER LOGIN Login_name WITH PASSWORD = 'my temp password'
MUST_CHANGE, CHECK_POLICY = ON, CHECK_EXPIRATION = ON;

If the post helped you, please share it:
Pin It

3 comments to How to List all SQL Logins with Passwords not Enforced by Policies

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">