none
How to allow users to change/reset their own password without OLD_PASSWORD required?

    Question

  • I want to allow users to change or reset their own password but OLD_PASSWORD 

    For example

    I have a user named "test" with password "1234" which I want him be able to change his password.

    So, I did

    GRANT ALTER ON LOGIN:: [test] to [test]

    and I tried logging in as login "test" then I tried

    ALTER LOGIN [test] WITH PASSWORD='12345' OLD_PASSWORD='1234'.

    It's worked.

    But in my case, I wan to know how to change password without the option OLD_PASSWORD.

    Anyone can help please? Thank you

    Pitithat



    • Edited by Pitithat Thursday, January 17, 2013 7:29 AM
    Thursday, January 17, 2013 4:21 AM

Answers

  • The minimum permission to change the password of a login without providing the login is the ALTER ANY LOGIN permission. This is a terrible choice for your purposes, because the ALTER ANY LOGIN permission will let them create new logins, drop logins, and change the password of any login, not just their own.

    Since each login knows their own password (and just used it to connect), having the requirement to provide the old password during a password change is considered appropriate. Note that Windows has the same requirement. You have to know your Windows password to change it, unless you are a high privileged user such as an administrator.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, January 17, 2013 4:42 PM

All replies


  • >>But in my case, User must not declare the option OLD_PASSWORD.


    What do you mean by user must not declare the OLD_PASSWORD? They should not just mention it while ALTER LOGIN?

    then try this, it should work without OLD_PASSWORD

    ALTER LOGIN [test] WITH PASSWORD='test2' 
    Regards
    Satheesh
    Thursday, January 17, 2013 5:36 AM
  • I mean how to change without using OLD_PASSWORD?

    By the way, without OLD_PASSWORD,  the system will show an error

    Cannot alter the login 'test', because it does not exist or you do not have permission.

    Regards


    • Edited by Pitithat Thursday, January 17, 2013 7:32 AM
    Thursday, January 17, 2013 7:27 AM
  • are you logged  with the [test] in that case you don't need the OLD_PASSWORD, it worked fine for me alteast

    ALTER LOGIN [test] WITH PASSWORD='test2' 

    Regards
    satheesh

    Thursday, January 17, 2013 7:38 AM
  • No, It doesn't work really. I've tried many times.
    Thursday, January 17, 2013 8:08 AM
  • What are the roles associated with the User? If its public only then i think it wont work.

    The option would be to specify the OLD_PASSWORD

    ALTER LOGIN [test] WITH PASSWORD='test2'  OLD_PASSWORD='XXXXXX'

    Regards
    satheesh

    Thursday, January 17, 2013 9:54 AM
  • The minimum permission to change the password of a login without providing the login is the ALTER ANY LOGIN permission. This is a terrible choice for your purposes, because the ALTER ANY LOGIN permission will let them create new logins, drop logins, and change the password of any login, not just their own.

    Since each login knows their own password (and just used it to connect), having the requirement to provide the old password during a password change is considered appropriate. Note that Windows has the same requirement. You have to know your Windows password to change it, unless you are a high privileged user such as an administrator.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, January 17, 2013 4:42 PM