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

SQL SERVER - Script Upgrade - Server Principal 'MS_PolicyEventProcessingLogin' Has Granted One or More Permission(s) error 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)

, , , ,
Previous Post
SQL SERVER – FIX : Msg 35295, Level 16 – Log Backup For Database ‘DBName’ on a Secondary Replica Failed
Next Post
SQL SERVER – PRINTing Status Using RAISERROR With NOWAIT Option

Related Posts

2 Comments. Leave new

  • Hi Pinal, what if we can’t find the grantee user in the database, i cannot find it on sys.server_principals ? how do we get rid of this permission?

    Reply
  • Hi Pinal, my server (2016) starts fine, but in the log file I see…
    The activated proc ‘[dbo].[sp_syspolicy_events_reader]’ running on queue ‘msdb.dbo.syspolicy_event_queue’ output the following: ‘Cannot execute as the database principal because the principal “##MS_PolicyEventProcessingLogin##” does not exist, this type of principal cannot be impersonated, or you do not have permission.’

    Reply

Leave a Reply

Menu