SQL Server 升級失敗，並傳回錯誤 15173 或 15559

本文可協助您針對安裝 Microsoft SQL Server 累積更新 （CU） 或 Service Pack （SP） 時發生的錯誤 15173 或 15559 進行疑難解答。 執行資料庫升級文本時發生錯誤。

徵兆

當您為 SQL Server 套用 CU 或 SP 時，安裝程式會報告下列錯誤：

等候 Database Engine 復原控制代碼失敗。 請檢查 SQL Server 錯誤記錄檔以了解潛在原因。

當您檢查 SQL Server 錯誤記錄檔時，您會注意到下列其中一個錯誤專案。

錯誤訊息集 1：

Error: 15173, Severity: 16, State: 1.
Server principal '##MS_PolicyEventProcessingLogin##' has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 15173, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
EventID 3417
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

錯誤訊息集 2：

Dropping existing Agent certificate ...
Error: 15559, Severity: 16, State: 1.
Cannot drop certificate '##MS_AgentSigningCertificate##' because there is a user mapped to it.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 15559, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master'database, it will prevent the entire SQL Server instance from starting.Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
Error: 3417, Severity: 21, State: 3.
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.</br>
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

原因

發生此問題的原因是升級腳本因為無法卸除伺服器主體 （或 ##MS_PolicyEventProcessingLogin## ） ##MS_AgentSigningCertificate##而停止執行。 發生此錯誤的原因是用戶已對應至伺服器主體。

如需在 CU 或 SP 安裝期間執行之資料庫升級腳本的詳細資訊，請參閱 針對套用更新時的升級腳本失敗進行疑難解答。

解決方法

若要解決 15173 或 15559 錯誤，請遵循下列步驟：

1. 使用追蹤旗標 （TF） 902 啟動 SQL Server。 如需詳細資訊，請參閱 使用追蹤旗標 902 啟動 SQL 的步驟。

2. 線上到 SQL Server，並執行下列其中一個查詢，視錯誤訊息中所述的伺服器主體而定：

   SELECT a.name, b.permission_name  
   FROM sys.server_principals a 
   INNER JOIN sys.server_permissions b ON a.principal_id = b.grantee_principal_id 
   INNER JOIN sys.server_principals c ON b.grantor_principal_id = c.principal_id 
   WHERE c.name = '##MS_PolicyEventProcessingLogin##'
   

   SELECT a.name, b.permission_name  
   FROM sys.server_principals a 
   INNER JOIN sys.server_permissions b ON a.principal_id = b.grantee_principal_id 
   INNER JOIN sys.server_principals c ON b.grantor_principal_id = c.principal_id 
   WHERE c.name = '##MS_AgentSigningCertificate##'
   

3. 針對查詢結果中顯示的每個登入，執行如下語句來撤銷這些許可權。

   例如，如果其中一個查詢傳回下列結果：

   名稱：許可權名稱 NT SERVICE\MSSQL$TEST： CONTROL

   在此情況下，請執行下列其中一個 語句：

   REVOKE CONTROL ON LOGIN::[##MS_PolicyEventProcessingLogin##] TO [NT SERVICE\MSSQL$TEST] AS [##MS_PolicyEventProcessingLogin##]
   

   REVOKE CONTROL ON LOGIN::[##MS_AgentSigningCertificate##] TO [NT SERVICE\MSSQL$TEST] AS [##MS_AgentSigningCertificate]
   

4. 從啟動參數中移除 TF 902，然後重新啟動 SQL Server。 在 SQL Server 在沒有 TF 902 的情況下啟動之後，升級腳本會再次執行。

   • 如果升級腳本順利完成，SP 或 CU 升級就會完成。 您可以檢查 SQL Server 錯誤記錄檔和啟動程式資料夾，以確認已完成的安裝。

   • 如果升級腳本再次失敗，請檢查 SQL Server 錯誤記錄檔中是否有其他錯誤專案，然後針對新的錯誤進行疑難解答。