Speak guys!
In this article I would like to share a situation that occurred during a customer service where I work, that is, another real case of daily life. We had a database instance that went into suspect mode (nothing to do with this post) and when I went to parse the SQL Server log for more information on the base that was marked suspect, I saw a lot of error occurrences 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 main database because the main “## MS_PolicyEventProcessingLogin ##” does not exist, this type of principal cannot be impersonated, or you do not have permission. '
Example:
Understanding the problem
Looking at the error message, we can see that this is probably a permission issue or the default system user “## MS_PolicyEventProcessingLogin ##” is orphaned. As I explained in more detail in the article Identifying and solving orphaned user problems in SQL Server with sp_change_users_login, a user is “orphaned” when he loses the association between the instance login and the bank user, usually after BACKUP / RESTORE processes or when a user's login is deleted.
Login ## MS_PolicyEventProcessingLogin ## is installed by default on SQL Server (disabled), responsible for some internal tasks in the instance, such as reading Service Broker events and policy management.
As we can see from this user's permissions, he has EXECUTE access on the Stored Procedure sp_syspolicy_events_reader:
If we try to execute this Stored Procedure as user ## MS_PolicyEventProcessingLogin ##, through an IMPERSONATE method (To learn more about IMPERSONATE, access this post), we see the following error message:
|
1 2 3 4 5 |
USE [msdb] GO EXECUTE AS USER = '##MS_PolicyEventProcessingLogin##' GO |
Msg 15517, Level 16, State 1, Line 1
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.
That is exactly the error message we are dealing with. Hmmm ..
These error messages in the SQL Server log are usually generated on instances that use Service Broker for message exchange (especially when there are MANY error logs frequently) after some database msdb BACKUP / RESTORE process.
If your instance does not use Service Broker, even if this user is orphaned, you probably do not notice this issue.
Identifying the problem
As I explained in the article Identifying and solving orphaned user problems in SQL Server with sp_change_users_login, to identify if user ## MS_PolicyEventProcessingLogin ## is orphaned, we can use the query below:
|
1 2 3 4 5 6 7 8 9 |
SELECT A.[name] AS UserName, A.[sid] AS UserSID FROM msdb.sys.database_principals A WITH(NOLOCK) LEFT JOIN sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid] WHERE B.[sid] IS NULL AND A.[name] = '##MS_PolicyEventProcessingLogin##' |
Result:
If the query returns any rows, it is because user ## MS_PolicyEventProcessingLogin ## is orphaned and we will need to fix this by associating the database user msdb with the corresponding login.
Simulating the problem
If you want to test and / or simulate this problem in your environment, simply enable Service Broker (enable Database Mail, for example) and run the following commands:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
USE [master] GO CREATE LOGIN [teste] WITH PASSWORD = 'teste123', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF GO USE [msdb] GO ALTER USER [##MS_PolicyEventProcessingLogin##] WITH LOGIN = [teste] GO DROP LOGIN [teste] GO EXECUTE AS USER = '##MS_PolicyEventProcessingLogin##' GO EXEC dbo.sp_syspolicy_events_reader GO REVERT GO |
After a few minutes, your SQL Server log will probably look like this:
Correcting the problem
Now that we understand the situation that is occurring and have identified that these errors generated in the SQL Server log are due to the user ## MS_PolicyEventProcessingLogin ## being orphaned, let's fix this problem.
To do so, simply associate the login with the user, with the command below:
|
1 2 3 4 5 |
USE [msdb] GO ALTER USER [##MS_PolicyEventProcessingLogin##] WITH LOGIN = [##MS_PolicyEventProcessingLogin##] GO |
You can also use sp_change_users_login to fix orphaned users:
|
1 2 3 4 5 |
USE [msdb] GO sys.sp_change_users_login 'Auto_Fix', '##MS_PolicyEventProcessingLogin##' GO |
Result:
The row for user '## MS_PolicyEventProcessingLogin ##' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.
Observation: I prefer to use ALTER USER because sp_change_users_login is marked deprecated and may be removed in future versions of SQL Server.
After making these changes, the messages must have stopped occurring in the SQL Server log. Since I'm stubborn, I want to make sure it's really working instead of waiting for the error messages to stop:
Now yes, I was calm. 🙂
Hope you enjoyed this article and see you next time!
Big hug!
Thank you. Helped me!