Sql-server – What would cause an orphaned ##MS_PolicyEventProcessingLogin##

loginssql serversql-server-2008

This morning I noticed my SQL Log was filling with the following message:

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.'

Running the following EXEC sp_change_users_login 'report' revealed that the login had in fact been orphaned.

I was able to fix it by running the following as recommended in this MSDN post.

EXEC sp_change_users_login 
    'Auto_Fix', '##MS_PolicyEventProcessingLogin##', 
    NULL, 'fakepassword'

But the question remains: What in the world would have caused this principal to have been orphaned in the first place? Googling and researching reveal that others have had this problem but I have yet to find a description of cause. Nothing of note that I am aware of occurred the moment the error began appearing.

We moved the whole server to a SAN storage model last summer, we restored everything (including msdb) during that move but that was months ago. It was only something recent that made the symptom manifest as it doesn't appear in the log earlier than a couple weeks ago.

Best Answer

We upgraded two servers (from SQL 2000) to SQL 2008R2 using in-place upgrade. We started getting these messages in SQL logs after upgrade. We did not change this or any other logins or users during the upgrade process.

My guess is upgrade process left two accounts (##MS_PolicyEventProcessingLogin## and ##MS_PolicyTsqlExecutionLogin##) orphaned.

EXEC sp_change_users_login 'Auto_Fix', '<User Name>' fixed this issue.

Related Question