none
Using 'Alter Login' to change password in SQL Server 2005

    Question

  • Please Help!!

    We have an application using SQLOLEDB connection to a SQL Server 2005 database.  Per domain policy, the users are required to change their password every 60 days.

    The accounts are established to 'Enforce password policy'.

    When we try to execute the 'ALTER LOGIN' command to change the password, locks are being established and will not free the account without bouncing the instance.

    After issuing the command, any interaction with the server using this UserID results in a "lock request time out" error 1222.

    I have tried issuing this command using both the application and through SQLServer Mgmt Studio Express and the results are the same.

    Any idea would be greatly appreciated.

     

    Rusty Rickmon

    Tuesday, September 26, 2006 11:58 AM

Answers

  •   Let me see if I understand your scenario; please, correct any assumption that is incorrect. You are creating a login with the password policy enforced option, then, after the password has expired (in your case 60 days) you are trying to change the password on one session, and then all the other sessions (connected with the same login/password) get the lock request timeout 1222 error. Correct?

     

      We haven’t seen this problem before. I hope you can help us by providing us with more descriptive steps to try to reproduce this problem.

     

      Thanks a lot,

    -Raul Garcia

     SDE/T

     SQL Server Engine

    Thursday, September 28, 2006 12:53 AM
    Moderator
  •   What is the error you are getting? Most likely, it is a syntax error referring to the usage of variables in the DDL. I answered a similar question regarding syntax errors and DDL parameterization on this other thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1154238&SiteID=1).

     

      Let us know if the information in this other thread is not useful for your particular scenario and/or if you have any additional questions.

     

      Thanks a lot,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Tuesday, March 20, 2007 12:17 AM
    Moderator

All replies

  •   Let me see if I understand your scenario; please, correct any assumption that is incorrect. You are creating a login with the password policy enforced option, then, after the password has expired (in your case 60 days) you are trying to change the password on one session, and then all the other sessions (connected with the same login/password) get the lock request timeout 1222 error. Correct?

     

      We haven’t seen this problem before. I hope you can help us by providing us with more descriptive steps to try to reproduce this problem.

     

      Thanks a lot,

    -Raul Garcia

     SDE/T

     SQL Server Engine

    Thursday, September 28, 2006 12:53 AM
    Moderator
  • I am also having problems with the Alter Login syntax. I have a bought package that uses SQL Server Authentication and I need to be able to give the users the ability to change their own password without giving them access to SQL Server Management studio.

    The Alter Login examples in SQL Server books online are not exactly helpful. The tech writers must be getting penalized for multiple line examples.

    I am assuming that you should be able to put the old password and new password into a single command:

    create PROCEDURE [dbo].[usp_change_password]

    /* ------------------------------------------------------------

    PROCEDURE: usp_change_password

    DESCRIPTION: Updates the SQL Server login password

    AUTHOR: Mike Greene

    ------------------------------------------------------------ */

    @login varchar(12)

    @old_password varchar(12),

    @new_password varchar(12)

    AS

    ALTER LOGIN @login WITH PASSWORD = @new_password

    OLD_PASSWORD = @old_password

    RETURN @@ERROR

     

    What is the separator it is looking for?

    Monday, March 19, 2007 6:33 PM
  •   What is the error you are getting? Most likely, it is a syntax error referring to the usage of variables in the DDL. I answered a similar question regarding syntax errors and DDL parameterization on this other thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1154238&SiteID=1).

     

      Let us know if the information in this other thread is not useful for your particular scenario and/or if you have any additional questions.

     

      Thanks a lot,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Tuesday, March 20, 2007 12:17 AM
    Moderator