Link orphaned logins to SQL Server

Hello

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:

USE msdb
Go
SELECT * FROM sys. database_principals WHERE [Name] = ‘ # #MS_PolicyEventProcessingLogin # # ‘
Go

Then we must associate it, and we do it with the following query:

Use msdb
Go
EXEC sp_change_users_login ‘ Auto_Fix ‘, ‘ # #MS_PolicyEventProcessingLogin # # ‘;
Go

After that it would work without problem.

We hope this has been useful for you

Greetings,

Database Team