missing login ##MS_PolicyEventProcessingLogin## and ##MS_PolicyTsqlExecutionLogin##

Question

missing login ##MS_PolicyEventProcessingLogin## and ##MS_PolicyTsqlExecutionLogin##

Heisenberg 266

hi Folks, On one of our sql server we are seeing missing logins ##MS_PolicyEventProcessingLogin## ##MS_PolicyTsqlExecutionLogin##. due to which we are getting error like "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.'" Any idea how can i recreate these logins?

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Erland Sommarskog 137.6K MVP Volunteer Moderator

What does these two SELECT return?

SELECT * FROM sys.server_principals WHERE name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##')
SELECT * FROM msdb.sys.database_principals WHERE name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##')

If both return data, is the contents in the sid columns the same? Probably not.

Heisenberg 266 Reputation points

2024-02-22T16:13:26.3166667+00:00

hello @Erland Sommarskog first query referencing view "sys.server_principals" doesnt return anything. msdb query returns two rows but owning_principal_id column has null value.
LiHongMSFT-4306 31,621 Reputation points

2024-02-29T06:53:10.2766667+00:00

Hi @Heisenberg Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer accepted by question author

Erland Sommarskog 137.6K MVP Volunteer Moderator

first query referencing view "sys.server_principals" doesnt return anything.

Seems like someone has dropped that login. Probably some sort of a lockdown craze.

So you need to recreate the logins. You can do it this way:

DECLARE @sql nvarchar(MAX) = 
   'CREATE LOGIN ##MS_PolicyEventProcessingLogin## WITH PASSWORD = ''' + convert(char(36), newid()) + 
      ''', SID = 0x413...
   ALTER LOGIN ##MS_PolicyEventProcessingLogin## DISABLE'
--PRINT @sql
EXEC(@sql)

Get the value that follows SID = from the sid column for the user in msdb.sys.database_principals.

The idea with newid() is that you get a random password that you never see. And never will need.

Heisenberg 266 Reputation points

2024-02-23T05:40:48.84+00:00

Thank you @Erland Sommarskog appreciate it. Can i do same thing for other login too. I am not getting any error for that but in case its required by any other process.
Erland Sommarskog 137.6K Reputation points MVP Volunteer Moderator

2024-02-23T08:12:12.05+00:00

The idea was that you would do the same thing for both accounts. If things work if you only do it for the account in the example, I guess you do not have to create that one. Then again, it could come back and bite you later.

2 additional answers

Erland Sommarskog 137.6K Reputation points MVP Volunteer Moderator

2024-02-21T22:30:06.6833333+00:00

What does these two SELECT return?

SELECT * FROM sys.server_principals WHERE name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##') SELECT * FROM msdb.sys.database_principals WHERE name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##')

If both return data, is the contents in the sid columns the same? Probably not.
Heisenberg 266 Reputation points

2024-02-22T16:13:26.3166667+00:00

hello @Erland Sommarskog first query referencing view "sys.server_principals" doesnt return anything. msdb query returns two rows but owning_principal_id column has null value.
LiHongMSFT-4306 31,621 Reputation points

2024-02-29T06:53:10.2766667+00:00

Hi @Heisenberg Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)
Heisenberg 266 Reputation points

2024-02-23T05:40:48.84+00:00

Thank you @Erland Sommarskog appreciate it. Can i do same thing for other login too. I am not getting any error for that but in case its required by any other process.
Erland Sommarskog 137.6K Reputation points MVP Volunteer Moderator

2024-02-23T08:12:12.05+00:00

The idea was that you would do the same thing for both accounts. If things work if you only do it for the account in the example, I guess you do not have to create that one. Then again, it could come back and bite you later.

Answer 1

Hi @Heisenberg Try EXEC sp_change_users_login 'Auto_Fix', '<User Name>'

Refer to this similar thread: What would cause an orphaned ##MS_PolicyEventProcessingLogin##?

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Kevin Snow 0

For me, the account gets orphaned each time the replica experiences an AvailabilityGroup failover. The account has the same SID in syslogins on all replicas. However, the SID in database_Principals is different in the master and msdb databases. It seems logical that sp_change_users_login is changing the server SID to match the database SID, so the problem reoccurs each time I switch Primary replicas. I should change the database principal SID to match the server login. Dropping and recreating the database user should do that.

Erland Sommarskog 137.6K Reputation points MVP Volunteer Moderator

2026-09-23T14:39:26.23+00:00

Please don't piggyback on old threads, but ask a new Question. In that question, please indicate whether this is a contained availability group or not.