Today we are going to expose you how to solve a problem that appears with some frequency, that of the orphaned logins. What we happen to revise the log SQL Server or to run a procedure after a migration, we find errors of the type:
The activated proc ‘ [dbo]. [Sp_syspolicy_events_reader] ‘ running on queue ‘ msdb. dbo. Syspolicy_event_queue ‘ Output the following: ‘ Cannot execute as the main database because the main “# #MS_PolicyEventProcessingLogin # #” Does not exist, this type of principal Cannot be Impersoned, or you do not have permission. ‘
This error occurs because we have a login created at the server level but it is not associated with a user database. In this case it is the “# #MS_PolicyEventProcessingLogin # #” login, which executes the procedure “[dbo]. [Sp_syspolicy_events_reader] “in the msdb system data base.
Is the login associated?
The first thing we should do is check if it exists, and we do it with:
SELECT * FROM sys. database_principals WHERE [Name] = ‘ # #MS_PolicyEventProcessingLog
Then we must associate it, and we do it with the following query:
EXEC sp_change_users_login ‘ Auto_Fix ‘, ‘ # #MS_PolicyEventProcessingLog
After that it would work without problem.
We hope this has been useful for you