[轉]SQL Server 如果本機管理者與 sa 帳戶都無法登入怎麼辦？

前天公司的開發用資料庫主機的硬碟異常，為了拯救資料所以我把整個資料庫主機重灌了，安裝好之後意外的把資料庫裡的本機管理者登入權限移除 (BUILTINAdministrators)，隔天公司其他人由於原本的帳號都登入不了，因此改用 sa 嘗試登入 (重灌後密碼變了)，結果卻嘗試到連 sa 帳戶都被鎖定，你說我該怎樣解決呢？^^

那天，同事打電話給我 (我因為熬夜救資料庫，所以睡晚)，半夢半醒之間他們告訴我一個噩耗，就是 sa 被鎖定了，而且本機管理者也登入不進資料庫。接著我嘗試登入資料庫，一整個驚醒，因為我真的也無法登入啊！以下是帳戶被鎖定的畫面： (注意: 要輸入正確的帳號密碼才會顯示帳號已被鎖定的訊息)

這個解法有好多個面向可以探討，而且不同版本的 SQL Server 也會有不同的處理方法：

※ SQL Server 2005 的解法

如果你的 SQL Server Agent 可以啟動的話，那問題就會簡單很多，你只要把要登入的帳號加入到 SQLServer2005 的 SQLAgentUser 群組內，就有 sysadmin 權限可登入了，如上圖所示，就是到本機使用者和群組新增授權的帳戶到SQL2008R2SQLServer2005SQLAgentUser$SQL2008R2$SQL2005 即可。

登入後，修改 sa 帳戶，將強制執行密碼原則 (Enforce password policy) 這個勾選項目拿掉即可，這個設定是讓 sa 帳戶立即取消「鎖定狀態」或變更密碼，就可以利用 sa 來登入了：

或者可以輸入以下 T-SQL 執行：

USE [master]
GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master], 
    DEFAULT_LANGUAGE=[us_english], 
    CHECK_EXPIRATION=OFF, 
    CHECK_POLICY=OFF
GO

USE [master]

ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master],

DEFAULT_LANGUAGE=[us_english],

CHECK_EXPIRATION=OFF,

CHECK_POLICY=OFF

這裡值得特別一提的就是強制執行密碼原則 (Enforce password policy)，當勾選的時候，該 SQL Server 帳戶就會使用本機安全性原則的「帳戶鎖定原則」去限制帳戶登入的次數與鎖定的時間，如下圖所示：

所以，就算你的 sa 帳戶被鎖定，其實你可以慢慢等 15 分鐘，該帳號就可以重新嘗試登入了！（當然，如果連密碼都忘記的話，那就用上述的解法吧！）

※ SQL Server 2008 / SQL Server 2008 R2 的解法

由於 SQL Server 2008 的安全性相較於之前版本都來的嚴謹，他是用服務群組的概念來授予 SQL Agent 權限 (服務群組無法透過 GUI 介面新增使用者)，因此無法透過上述 SQL Server 2005 的方式來跳過驗證，相對的解決方法也困難許多。（單純密碼打錯而被鎖定者除外，因為你只要等 15 分鐘就可以了）

如果真的連密碼都忘記，可以參考以下辦法：

1. 重新安裝 SQL Server，也就是常見的 3R 策略 (Reboot, Reformat, Reinstall)！

2. 找到舊有的 master 資料庫備份資料，並還原到你記得密碼的那個資料庫版本。

3. 讓 SQL Server 進入單一使用者模式 (single-user mode)，即可透過任意「本機管理者」登入資料庫！

以下是啟用單一使用者模式的方法：

在進階 (Advanced) 頁籤的啟動參數 (Startup Parameters) 欄位的最前面加上 -m; 並按下套用然後重新啟動服務：

啟動時請注意要先將 SQL Server Agent 服務給停止，否則因為單一使用者模式的關係，只要 SQL Server Agent 服務成功登入 SQL Server 你就無法再進入資料庫作維護了：

最後開啟 Management Studio (ssms) 後新增查詢，並執行以下 T-SQL 語法即可將本機 Administrator 管理者加入到 master 資料庫中，之後再修改啟動參數，將 -m; 移除即可讓 SQL Server 正常登入了。

USE [master]
GO
CREATE LOGIN [BUILTINAdministrators]
FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember 
    @loginame = N'BUILTINAdministrators', 
    @rolename = N'sysadmin'
GO

USE [master]

CREATE LOGIN [BUILTINAdministrators]

FROM WINDOWS WITH DEFAULT_DATABASE=[master]

EXEC master..sp_addsrvrolemember

@loginame = N'BUILTINAdministrators',

@rolename = N'sysadmin'

也是因為單一使用者模式的關係，在開啟 Management Studio 之後請不要試著先連接資料庫再新增查詢，而是直接新增查詢再登入資料庫！如果先連接資料庫的話，這個「單一使用者」就被你用掉了，所以會讓你無法再執行上述 T-SQL 語法，請參考以下步驟執行：

先執行新增查詢

這時才進行登入

這時才能夠執行 T-SQL

心得分享

這次的經驗又再次驗證 IT 人員的重要性，平常主機沒事的時候，大家都覺得當 IT 很輕鬆，平常沒事就在看書、做 Lab，一副輕鬆自在的樣子！（大家真的有這樣做嗎？還是裝忙居多！）

像我那天熬夜弄到凌晨五點，原本認為隔天早上不會被發現主機出狀況的，但隔天一早還是被叫醒，我想我比較幸運一點，在公司沒人會罵我，但我可以想像有許多 IT 人都是在做到三更半夜然後隔天還是因為出狀況而被主管或客戶罵到臭頭的，在此我真的能夠感同身受。

所以說越專業的 IT 人員，應該是越讓人感覺不到他的存在，那才是真價值，不過有多少老闆能有這樣的觀念呢？我也認識許多 IT 人，知道許多公司會讓 IT 人員處理許多雜事（因為被老闆認為太閒），因此就開始裝忙，反而疏於精進 IT 技術，我覺得這不是好事，但也許環境讓你身不由己，但環境是個人的選擇，也怪不得他人了。

我覺得 IT 人員平常沒壓力時應該不斷精進技術，並多做 Lab 培養實戰經驗，當 IT 系統出狀況時，可以用最短的時間解決問題，那才是真正專業的 IT 人員，其個人價值也才能真實呈現，你覺得呢？ 🙂

轉自：http://blog.miniasp.com/post/2011/03/08/SQL-Server-When-System-Administrators-and-sa-Are-Locked-Out.aspx

其他文章：

昨天在测试一些权限今天早上来就发现SQL SERVER 登陆不上去，报错为: 用户登陆失败：消息 18456，级别 14，状态 1，服务器 XXX，第 1 行 . 用户 ‘XXX’ 登录失败。我的服务和代理都是自动启动的，所以问题不在这了，由于是本地服务，也没有多个帐户，测试的时候Sa也被自己禁用了。有的用户就是部分库的只读权限，怎么办呢?

方法就是：把 sql 启动到单用户模式，然后用 sqlcmd -A 登录，前提是你需要有电脑的管理员权限。网上有很多方法是一样的，但是对于单用户模式的说明都不太详细，至少对于像我这种小白来讲还是有一定的理解问题，所以写一下以便于像我一样误操作导致的同学进行学习。

启用本地帐户：
1.先看一下本机的帐户是否具有管理员的权限，如果没有添加上。
2.在开始菜单的搜索框中输入 cmd , 右键单击选择以管理员身份运行
3.在命令提示符输入 NET STOP MSSQLSERVRE 停止MSSQLSERVER运行（若已经停止则可以跳过此步骤）
4.若3有问题，提示报错，则可以在开始 –>SQL SERVER –> 配置工具 –>SQL SERVER 配置管理器 –> 打开SQL SERVER属性–>高级 –> 启动参数里面加上 -m
加参数的时候注意一下，一定要加在启动参数的最后面并且加上分号，以便于之前的相隔开来。如： -dD:\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\DATA\mastlog.ldf ;-m
5.若以上均无问题，则切换到安装路径，即Binn下sqlservr.exe的路径
如：cd C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn
6.执行 sqlservr.exe,即单用户模式进入了
7.再以管理员帐户重新登陆开启一个窗口，输入SQLCMD -A
8.输入你要更改的操作命令即可，在此处我需要的是把本机帐户添加 , 如：

USE master
GO
CREATE LOGIN [domain\username] FROM WINDOWS WITH DEFAULT_DATABASE=[Master]
GO
EXEC sp_addsrvrolemember @loginame=N'domain\username', @rolename=N'sysadmin'
GO

USE master

CREATE LOGIN [domain\username] FROM WINDOWS WITH DEFAULT_DATABASE=[Master]

EXEC sp_addsrvrolemember @loginame=N'domain\username', @rolename=N'sysadmin'

为了避免错误，可以再加一个SQL的帐户以备不时之需，也可以把sa命令启用 :
— 添加用户T1并给予管理员的权限

USE [master]
GO
CREATE LOGIN [T1] WITH PASSWORD=N'T1@123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'T1', @rolename = N'sysadmin'
GO

USE [master]

CREATE LOGIN [T1] WITH PASSWORD=N'T1@123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

EXEC master..sp_addsrvrolemember @loginame = N'T1', @rolename = N'sysadmin'

— 启用SA

ALTER LOGIN [sa] ENABLE
GO

1 2	ALTER LOGIN [sa] ENABLE GO

9.以上操作完成之后关闭2个命令行窗口，启动sqlserver，即可以登陆。

注：此环境为win7 + sql server 2008 其它环境没有尝试，仅作参考

發佈留言 取消回覆

發佈留言取消回覆