Monday, September 08, 2008

Change password for Login in SQL Server

Use system stored procedure 'sp_password' to change password. This procedure is used to add or modify a password for a Microsoft Sql Server login. Take a new query window and execute this procedure.Syntax :
sp_password 'Old Password','new password','login name'

Sample :
1. If login is currently with a blank password you can add a password for it. Adding a password for login 'sa'.
sp_password NULL,'Sql2005','sa'2. If login is having a password already then you can change password. Changing password for login 'sa'.
sp_password 'Sql2005','amthlk','sa'
Errors
Possible errors you can see when you try to change password are :-
1. Password validation failed. The password does not meet policy requirements because it is not complex enough.2. Password validation failed. The password does not meet policy requirements because it is too short.
When you create a SQL Server login, the server will validate the password against the password policy of the local machine. To view or modify the password policy on your lacal machine, take 'Administrative Tools' in 'Control Panel'. You can see 'Local Security Policy' in 'Administrative Tools'. Open this by clicking. You can see 'Password Policy' under 'Account Policy'.
Changing password policy or using a password that meet the password policy will clear error.


You can Use CHECK_POLICY option to disable password policy validation.
Use ALTER LOGIN to configure policy application.

The following SQL statement will change password of login to a new password without knowing old password. It requires ALTER ANY LOGIN permission to execute.

ALTER LOGIN sa WITH PASSWORD = 'sql2005', CHECK_POLICY = OFF

Changing login password from old password to new one.
ALTER LOGIN sa WITH PASSWORD = 'amthlk' OLD_PASSWORD = 'sql2005';