How to reset forgotten sa password 重設遺失的sa密碼

How to reset forgotten sa password 重設遺失的sa密碼

How to reset forgotten sa password

如何重設sa密碼  或 忘記sa密碼的情況下加入一個SQL管理員帳戶

---

如果忘記sa密碼，有2個方法解決

方法一

不需要重啟SQL Server服務，但Login裡面必須要原本就有NT AUTHORITY\SYSTEM帳戶，且此帳戶要有sysadmin Role

1.下載Sysinternals的psexec.exe工具

https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

2.啟動命令提示字元，用psexec.exe來啟動SSMS，就可以用NT SERVICE\SYSTEM身分Windows驗證登入

PsExec.exe -s -i "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

SQL Server Management Studio default locations 執行檔預設路徑與位置

方法二

處理過程會有downtime，因為需重新啟動SQL Server服務。

1.停止SQL Server服務

2.啟動第1個命令提示字元，以Single user mode啟動SQL Server

Start the SQL Server instance using single user mode (or minimal configuration which will also put SQL Server in single user mode)

SQLServr.Exe –m (or SQLServr.exe –f)

for example:

3.在SQL Server本機，啟動第2個命令提示字元，使用sqlcmd用Windows驗證直接登入

SQLCMD –S <Server_Name\Instance_Name>

4.建立一個新Login並給予sysadmin role

create a new account or add an existing login to SYSADMIN server role

CREATE LOGIN [dbasa] with 
PASSWORD= N'dbasa_P@ssw0rd',
CHECK_POLICY = OFF
GO
sp_addsrvrolemember 'dbasa', 'SYSADMIN'
GO

5.使用Ctrl+C來第1個命令提示字元的sql server

6.正常啟動SQL Server，就可以用剛剛建立的dbasa管理員帳戶登入，接下來如果需要，就去改掉sa密碼。

Reference:

Tips & Tricks: YOU HAVE LOST ACCESS TO SQL SERVER. NOW WHAT?
https://blogs.technet.microsoft.com/sqlman/2011/06/14/tips-tricks-you-have-lost-access-to-sql-server-now-what/

How to verify and change the system administrator password in MSDE or SQL Server 2005 Express Edition
https://support.microsoft.com/en-us/help/322336/how-to-verify-and-change-the-system-administrator-password-in-msde-or-sql-server-2005-express-edition

CREATE LOGIN (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql

Recover a lost SA password

https://www.sqlshack.com/recover-lost-sa-password/