SQL SERVER – Error 15559 – Error 912 – Script Level Upgrade for Database ‘master’ Failed

Recently, I assisted one of my clients in performing an upgrade from SQL Server 2005 to SQL Server 2014. Once the upgrade is complete, SQL Server service was unable to start. As usual, I always look at ERRORLOG and we could see the below logs in the SQL Server Error Logs. Here is the blog post which can help you to find the location of the error log: Where is ERRORLOG? Various Ways to Find its Location. While looking at the error log I found error related to Script Level Upgrade with Error 15559 – Error 912.

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 ‘sqlagent90_sysdbupg.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 the upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous error log entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

I recalled that a few months back, I wrote below blog because the error message we saw was similar: Unable to Start SQL Server After Patching.

The way to fix any upgrade issue would be to start SQL Server with trace flag 902 and then fix the error which caused the script to fail. Here are the steps taken to fix the issue for my client.

1. Started the Instances by adding the trace flag -T902 to the startup parameters.
2. Taken backup of all system databases.
3. Run following script

SELECT principal_id
FROM sys.server_principals
WHERE name = '##MS_AgentSigningCertificate##'

Above query returned 284 as a principal id

4. Looked for all permissions that above principal_id has granted to others.

SELECT *
FROM sys.server_permissions
WHERE grantor_principal_id = 284

Above query did not return any rows.

5. Found user mapped to the certificate.:

USE msdb
GO
SELECT *
FROM sys.syslogins a
INNER JOIN sysusers b ON a.NAME = b.NAME
WHERE b.NAME = '##MS_AgentSigningCertificate##'

Above query gave us username.

6. We drop the mapped user

Once above was dropped, we removed the trace flag -T902 from the startup parameters and then we were successfully able to bring up the SQL Server service.

Have you encountered similar errors during the upgrade? How did you fix them? Please comment and share with others. Connect on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)