SQL SERVER – Script Upgrade – Server Principal ‘MS_PolicyEventProcessingLogin’ Has Granted One or More Permission(s)

One should be very careful while working with system objects in SQL Server. Below is one of the situation where some permissions were given on system objects which was causing SQL startup failure. I have written various blog on script upgrade mode of SQL Server.

Whenever a patch is applied to SQL Server, one of the steps after first start is to run upgrade scripts. Those  scripts make changes to system objects. If there is any fatal error in running those scripts, SQL Server would not be able to start.

Below is the ERRORLOG snip from one of the failures.

2017-01-30 17:18:22.44 spid3s Error: 15173, Severity: 16, State: 1.

 2017-01-30 17:18:22.44 spid3s Server principal ‘##MS_PolicyEventProcessingLogin##’ has granted one or more permission(s).
 Revoke the permission(s) before dropping the server principal.

 2017-01-30 17:18:22.44 spid3s Error: 912, Severity: 21, State: 2.

 2017-01-30 17:18:22.44 spid3s 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.

 2017-01-30 17:18:22.44 spid3s Error: 3417, Severity: 21, State: 3.

 2017-01-30 17:18:22.44 spid3s 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.

The last message is very scary! We need to look back and look at meaningful error which is 15173 in this case. We should note that ##MS_PolicyEventProcessingLogin## is a certificate-based SQL Server login for internal system use only. It’s created from certificates when SQL Server is installed.

WORKAROUND/SOLUTION

In script failure, we always need to start SQL with trace flag 902, which would cause SQL to skip running those scripts and we get a chance to fix the issue. Here are the steps we followed to start SQL with trace flag.

• Open SQL Server Configuration Manager
• Select the SQL server instance in SQL Server Services,
• Right-click the instance, and then click Properties.
• Click the Startup Parameter tab.
• Add “-T902”

Then we started the SQL Server service. Since we have issue with ##MS_PolicyEventProcessingLogin##

SELECT * FROM   sys.server_permissions
WHERE grantor_principal_id = (SELECT principal_id
                               FROM sys.server_principals
                               WHERE NAME = N'##MS_PolicyEventProcessingLogin##')

Once we ran above query, we found that there was a login having permissions on this login and due to that SQL setup was unable to drop it.

Once we cleared dependency (using the REVOKE command), we were able to remove trace flag and start SQL without any error.

Reference:Pinal Dave (http://blog.SQLAuthority.com)