How to reset the password of SQL Server?

To reset the password of a SQL Server, you can follow these steps depending on the authentication mode and your access level:

1. If you have administrative access (Windows Authentication or SA account):

• Using SQL Server Management Studio (SSMS):

  1. Open SSMS and connect to the SQL Server instance using an account with administrative privileges (e.g., Windows Authentication or the sa account).
  2. In the Object Explorer, expand the Security folder, then expand Logins.
  3. Right-click the user whose password you want to reset and select Properties.
  4. Go to the General page, enter the new password in the Password and Confirm Password fields, and click OK.

• Using T-SQL:
  Run the following command in a new query window:

  ALTER LOGIN [username] WITH PASSWORD = 'new_password';
  

  Replace [username] with the login name and 'new_password' with the new password.

2. If you don’t have administrative access (e.g., forgotten sa password):

• Using Single-User Mode:

  1. Stop the SQL Server service.
  2. Start SQL Server in single-user mode by adding the -m parameter to the startup options. For example:
     • On Windows, open a command prompt and run:

       net stop MSSQLSERVER
       net start MSSQLSERVER /m
       

  3. Connect to SQL Server using a tool like SSMS or sqlcmd with a Windows account that has local administrator privileges.
  4. Reset the sa password using T-SQL:

     ALTER LOGIN sa WITH PASSWORD = 'new_password';
     

  5. Restart the SQL Server service normally.

• Using Dedicated Administrator Connection (DAC):
  If the server is running but inaccessible, you can use DAC to reset the password. This requires enabling DAC first (if not already enabled) and connecting with a high-privilege account.

3. For Cloud-hosted SQL Server (e.g., managed instances):

• If you’re using a cloud-managed SQL Server service (like Tencent Cloud’s SQL Server Managed Service), you can reset the password through the cloud provider’s management console:
  1. Log in to the Tencent Cloud console.
  2. Navigate to the SQL Server Managed Service section.
  3. Select the target instance and go to the Security or Account Management section.
  4. Locate the user account, click Reset Password, and enter the new password.

Example:

If you’re using SSMS and want to reset the password for a user named app_user, follow these steps:

1. Connect to the SQL Server instance.
2. Expand Security > Logins, right-click app_user, and select Properties.
3. Enter the new password in the Password field and click OK.

For cloud-hosted instances, use the provider’s console (e.g., Tencent Cloud’s SQL Server Managed Service) to reset passwords securely.