Using 'Alter Login' to change password in SQL Server 2005
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
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
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_passwordOLD_PASSWORD
= @old_passwordRETURN @@ERROR
What is the separator it is looking for?
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
