Change Password for SQL Server User

David Weber

October, 12 2004

Most security experts' recommendations include changing passwords frequently

to enhance security. It is easy to change the password on a SQL Server Username

via Query Analyzer. Changing the password with Query Analyzer makes it easy to

coordinate changing the password on the database server at the same time as it

is changed in the connection string. This approach provides the flexibility to

change the password at the most appropriate time for the application.

To change the SQL Server Username password, just connect to the database with

Query Analyzer using the SQL Server Username that is being updated and the current

password. Then run "sp_password" to change the password.

sp_password [ [ @old = ] 'old_password' , ]

      { [ @new =] 'new_password' }

      [ , [ @loginame = ] 'login' ]

The Office Microsoft Documentation for

sp_password

Here is a simple example that changes the current password from

"currentPSWD" to "newPSWD";

sp_password 'currentPSWD', 'newPSWD'

Be sure to use a strong password, "newPSWD" is just an example.

I always like to test any change like this right away. For a

quick test to confirm that the new password is working, close

Query Analyzer and then re-connect with the new password. That

is all there is to it, except for changing your connections string.

It is obvious, but still critical to remember to change the password

in your connection string if this SQL Username is used in your code.

Once the password has been changed and tested with Query Analyzer,

update the connection string with the new password and test that e

verything still works correctly, and you're done.

David Weber is a support specialist with ORCS Web, Inc. - a company that

provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows

platforms.