In SQL Server, use sp_password to reset the SA password instance and sp_passwordsa

In SQL Server, use sp_password to reset the SA password instance and sp_passwordsa

The following error occurs when the SA password of SQL2000 cannot be changed:
Copy codeThe Code is as follows: Error 21776: [SQL-DMO] the name 'dbo' was not found in The Users collection. if the name is a qualified name, use [] to separate varous parts of the name, and try again.

Solution: Use the SQL query manager to connect to the SQL, and execute:
Copy codeThe Code is as follows: EXEC sp_password NULL, 'xxxxxx', 'sa'

The specific information about Sp_password is as follows:

Sp_password

Add or change Microsoft? SQL Server? The logon password.

Syntax

Sp_password [] [@ old =] 'old _ password',]
{[@ New =] 'new _ password '}
[,] [@ Loginame =] 'login']

Parameters

[@ Old =] 'old _ password'
Is the old password. Old_password is of the sysname type, and its default value is NULL.
[@ New =] 'new _ password'
Is the new password. New_password is of the sysname type and has no default value. If the name parameter is not used, you must specify old_password.
[@ Loginame =] 'login'
Is the login name affected by the password change. Login is of the sysname type, and its default value is NULL. Login must already exist and can only be specified by members of the sysadmin fixed server role.

Return code value

0 (successful) or 1 (failed)

Note

The SQL Server password can contain 1 to 128 characters, including any letters, symbols, and numbers.
The new password is updated and stored in encrypted form. Therefore, no user (or even System Administrator) can view the password.

When members of sysadmin or securityadmin fixed server roles use sp_password with all three parameters to reset their own passwords, the review records reflect that they are changing others' passwords.

Sp_password cannot be used in Microsoft Windows NT? Security Account. Users who connect to SQL Server through a Windows NT network account are authorized by Windows NT, so their passwords can only be changed in Windows NT.

Sp_password cannot be executed in a user-defined transaction.

Permission

By default, the execution permission is granted to the public role for users to change their logon passwords. Only members of the sysadmin role can change the logon password of other users.

Example

A. Change the logon password without the original password
The following example changes the password for logging on to Victoria to OK.
Copy codeThe Code is as follows: EXEC sp_password NULL, 'OK', 'victoria'
B. Change the password
In the following example, the password for logging on to Victoria is changed from OK to coffee.
Copy codeThe Code is as follows: EXEC sp_password 'OK', 'coffee'

How to reset the sa password for SQL server 2005

In SQL Server Management Studio, after logging on with a windows Account, choose security-Logon Name-find sa, right-click to familiarize yourself
Or use the command line sa or sp_passwd null, newpassword, and sa after logging on to windows.

How can I restore the SQL2000 sa password to SA?

Go directly to the Enterprise Manager-Manage-login-user and change the password you want.