locked
CREATE LOGIN RRS feed

  • Question

  • CREATE LOGIN [usertest] WITH PASSWORD=N'Changemypass1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

    In regards to check_policy=on
    The server local policy is enabled with a password history of 12.

    Why is it I can reset the password to the same password without resistance or error.

    Wednesday, April 29, 2009 1:46 PM

Answers

  • Password resets are administrative actions, and requires ALTER ANY LOGIN permission. If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission.
    So, if end user don't have ALTER ANY LOGIN or CONTROL SERVER permission, OLD_PASSWORD parameter must be specified.

    Hope this help.

    Wednesday, April 29, 2009 3:40 PM

All replies

  • Hi,

    CHECK_EXPIRATION and CHECK_POLICY are only enforced on Windows Server 2003 and later.
    Wednesday, April 29, 2009 2:00 PM
  • Agreed.
    We run Windows Server 2003 exclusively.
    Thats not the problem.
    Wednesday, April 29, 2009 2:08 PM
  • If you changing password with
    ALTER LOGIN LoginName WITH Password = 'password' that is password reset. Password resets are administrative actions and they bypass password history checks.
    Password history checks happen in SQL Server when the OLD_PASSWORD parameter is specified as well.

    Wednesday, April 29, 2009 2:19 PM
  • so to by pass the 'rule' then the user just has to use less of a statement.
    Is there a way to force the end user to use OLD_PASSWORD?

    Otherwise isn't this a gaping hole in the model?
    Wednesday, April 29, 2009 2:48 PM
  • I should also mention that when changing the password via SSMS gui, it does not seem to sue the Old_Password parameter...
    Wednesday, April 29, 2009 3:28 PM
  • Password resets are administrative actions, and requires ALTER ANY LOGIN permission. If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission.
    So, if end user don't have ALTER ANY LOGIN or CONTROL SERVER permission, OLD_PASSWORD parameter must be specified.

    Hope this help.

    Wednesday, April 29, 2009 3:40 PM