搜尋本站文章

2009-11-18

忘記 sa 密碼,也無法使用 Windows 認證的 Administrator 帳戶來登入 SQL Server。I forgot my SA password and cannot login as administrator using windows authentication.



若是遇到以下的情境,將導致無法登入到 SQL Server:


1. 屬於 sysadmin 固定伺服器角色之成員的所有登入,都因為錯誤而遭到移除。
2. 屬於 sysadmin 固定伺服器角色之成員的所有 Windows 群組,都因為錯誤而遭到移除。
3. 屬於 sysadmin 固定伺服器角色之成員的員工都已經離職,但沒有交接相關資訊。
4. sa 帳戶已遭到停用或是沒有人知道密碼。






已測試過的適用版本:
SQL Server 2005、2008


有數種的解決方法,例如:
1. 重新安裝 SQL Server。
2. 將另外一個執行個體的 master 資料庫的實體檔案(master.mdf、mastlog.ldf),直接覆蓋掉此受損的執行個體之 master 資料庫。

之後再利用先前對此執行個體的 master 資料庫備份來還原,但這些作法,可能相當耗時。
此外,如果 master 資料庫的備份較舊,可能不會有所有的資訊。
如果 master 資料庫的備份比較新,可能與先前的執行個體擁有相同的登入,因此,系統管理員仍然會遭到鎖定。




在 SQL Server 2008 線上叢書內的「疑難排解:當系統管理員遭到鎖定時連接到 SQL Server」一文,有提供一項節省時間的作法:1. 以單一使用者模式使用 -m 或 -f 選項啟動 SQL Server 的執行個體。
2. 接著,電腦本機管理員群組的任何成員都可以利用 sysadmin 固定伺服器角色的成員身分,連接到 SQL Server 的執行個體。




SQL Server 服務啟動選項之說明:
-f
啟動只含最小組態的 SQL Server 執行個體。
如果組態值設定 (如過度調配記憶體) 造成伺服器無法啟動,這就很有用。
以最低組態模式啟動 SQL Server 會將 SQL Server 放在單一使用者模式下。如需詳細資訊,請參閱隨後的 -m 描述。

-m
當您以單一使用者模式啟動 SQL Server 執行個體。以單一使用者模式啟動 SQL Server 執行個體時,只有單一使用者可以進行連接,且不會啟動 CHECKPOINT 處理序。
CHECKPOINT 會保證將交易規律地從磁碟快取區寫到資料庫裝置。(一般而言,如果遇到一些應該修復系統資料庫的問題時,就會使用這個選項)。
啟用 sp_configure allow updates 選項。根據預設,allow updates 是停用的。
在單一使用者模式下啟動 SQL Server 可讓電腦本機管理員群組的任何成員以 sysadmin 固定伺服器角色的成員身分,連接到 SQL Server 的執行個體。



實作練習:
任務1:停用 SQL Server 與 SQL Server Agent Service
當您以單一使用者模式啟動 SQL Server 執行個體時,請先停止 SQL Server Agent 服務。
否則,SQL Server Agent 有可能會先進行連接,您就無法以另一個使用者的身分進行連接。

任務2:以「單一使用者模式(single user mode)」來啟動 SQL Server 執行個體1. 在命令提示列(Command Prompt)中,執行以下的範例程式碼:
net start mssqlserver /m


上述程式碼是以預設的執行個體為例。若是為 SQLEXPRESS 版本,且安裝為具名執行個體個體時,可能的範例程式碼如下:
net start mssql$sqlexpress /m






任務3:使用本機 Windows 的 Administrators 管理群組之成員來連線 SQL Server
使用本機 Windows 的 Administrators 管理群組之成員來連線 SQL Server 後,就可以各項作業,例如:
1. 重新設定 sa 帳戶的密碼。
2. 啟用 sa 帳戶。
3. 設定「伺服器驗證」模式,例如:修改為「SQL Server 及 Windows 驗證模式」,也就是「混合(mixed)模式」。
4. 加入指定的 Windows 帳戶到 sysadmin 固定伺服器角色內。
... 等等。


1. 使用 SSMS 管理工具連線 SQL Server。
這項 SSMS 管理工具雖然很方便,但可能產生多條的連線來執行,將會導致您無法調整所需要的設定。







2. 使用 sqlcmd 模式來登入系統,這會是可以確保單一使用者連線的作法,以下提供數種範例程式碼:
-- 重新設定 sa 帳戶的密碼為:P@ssw0rd。
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'P@ssw0rd'
GO

-- 啟用 sa 帳戶
USE [master]
GO
ALTER LOGIN [sa] ENABLE
GO

-- 修改為「SQL Server 及 Windows 驗證模式」,也就是「混合(mixed)模式」
/*
1 是指:「Windows 驗證模式」
2 是指:「SQL Server 及 Windows 驗證模式」
*/
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO


任務4:關閉 SQL Server,再一般模式來重新啟動 SQL Server
經過上述的步驟,應該可以讓系統恢復正常登入方式。


參考資料:
疑難排解:當系統管理員遭到鎖定時連接到 SQL Server
http://msdn.microsoft.com/zh-tw/library/dd207004.aspx

使用 SQL Server 服務啟動選項
http://msdn.microsoft.com/zh-tw/library/ms190737.aspx

以單一使用者模式啟動 SQL Server
http://msdn.microsoft.com/zh-tw/library/ms188236.aspx

sp_password (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms174428.aspx

如何驗證與變更 MSDE 系統管理員密碼
http://support.microsoft.com/kb/322336

3 則留言:

  1. 分享心得,互相學習...^_^

    回覆刪除
  2. 謝謝德瑞克老師分享寶貴知識.

    我剛好遇到 sa 無法登入 SQL 的問題, 照著文章的指示進行, 總算解決了這個煩了我一個下午的問題!

    Tks a lot !

    回覆刪除