How can you use SP_PASSWORD to change a user's password?
MaryJns
Posted by MaryJns
Last Updated: June 21, 2024
SQL Password
In SQL Server, the SP_PASSWORD stored procedure is used to change a user's password. It's one of the methods available for managing user accounts within SQL Server. Below is the general syntax and an example of how to use SP_PASSWORD to change a user's password:
Syntax
EXEC sp_password 
    [ @old_password = 'old_password' ], 
    [ @new_password = 'new_password' ], 
    [ @loginame = 'login_name' ]
Parameters
- @old_password: The current password of the user. If you are an administrator changing the password without knowing the old password, this can be set to NULL. - @new_password: The new password that you want to assign to the user. - @loginame: The name of the login for the user whose password you want to change.
Example
Let's say you want to change the password for a user named 'john_doe'. If you know the current password, the command would look like this: 
EXEC sp_password 
    @old_password = 'current_password', 
    @new_password = 'new_strong_password', 
    @loginame = 'john_doe';
If you are an administrator and want to reset the password without knowing the old password, you can omit the old password parameter like this: 
EXEC sp_password 
    @old_password = NULL, 
    @new_password = 'new_strong_password', 
    @loginame = 'john_doe';
Notes
- The new password must meet the security policy of the SQL Server instance, which might include requirements for minimum length, complexity, etc. - SP_PASSWORD is marked as deprecated in newer versions of SQL Server. It is recommended to use the ALTER LOGIN statement for changing passwords in newer SQL Server environments.
Recommended Alternative
Using ALTER LOGIN is the preferred method in newer SQL Server versions. Here’s how you could do it: 
ALTER LOGIN john_doe WITH PASSWORD = 'new_strong_password';
This command will change the password for the user john_doe without needing to know the old password.
Conclusion
While SP_PASSWORD can still be used in SQL Server, it is advisable to switch to ALTER LOGIN for changing passwords as it is more secure and aligns with the direction of SQL Server's development.
Default Image