Fala pessoal!
Nesse artigo eu gostaria de compartilhar uma situação que ocorreu durante um atendimento a um cliente onde eu trabalho, ou seja, mais um caso real do dia a dia. Tivemos uma ocorrência de database que entrou no modo suspect (nada a ver com esse post) e quando fui analisar o log do SQL Server para procurar mais informações sobre a base que ficou marcada como suspect, vi um monte de ocorrências de erro com a seguinte mensagem:
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.’
Exemplo:
Entendendo o problema
Analisando a mensagem de erro, podemos ver que provavelmente isso se trata de problema de permissão ou o usuário padrão de sistema “##MS_PolicyEventProcessingLogin##” está órfão. Como expliquei em mais detalhes no artigo Identificando e resolvendo problemas de usuários órfãos no SQL Server com a sp_change_users_login, um usuário fica “órfão” quando ele perde a associação entre o login da instância e o usuário do banco, geralmente após processos de BACKUP/RESTORE ou quando o login de um usuário é excluído.
O login ##MS_PolicyEventProcessingLogin## vem instalado por padrão no SQL Server (desativado), responsável por algumas tarefas internas na instância, como a leitura de eventos do Service Broker e gerenciamento de policies.
Como podemos observar nas permissões desse usuário, ele possui acesso de EXECUTE na Stored Procedure sp_syspolicy_events_reader:
Se tentarmos executar esse Stored Procedure como o usuário ##MS_PolicyEventProcessingLogin##, através de um método de IMPERSONATE (Para saber mais sobre IMPERSONATE, acesse este post), vemos a seguinte mensagem de erro:
|
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.
Que é exatamente a mensagem de erro que estamos lidando. Hummm..
Essas mensagens de erro no log do SQL Server geralmente são geradas em instâncias que utilizam o Service Broker para troca de mensagens (especialmente quando existem MUITOS registros de erro frequentemente) após algum processo de BACKUP/RESTORE do database msdb.
Caso a sua instância não utilize o Service Broker, mesmo que esse usuário fique órfão, provavelmente você não perceba esse problema.
Identificando o problema
Conforme já expliquei no artigo Identificando e resolvendo problemas de usuários órfãos no SQL Server com a sp_change_users_login, para identificar se o usuário ##MS_PolicyEventProcessingLogin## está órfão, podemos utilizar a query abaixo:
|
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##' |
Resultado:
Se a query retornar alguma linha, é porque o usuário ##MS_PolicyEventProcessingLogin## está órfão e precisaremos corrigir esse problema, associando o usuário do database msdb ao login correspondente.
Simulando o problema
Caso você queira testar e/ou simular esse problema em seu ambiente, basta ativar o Service Broker (ative o Database Mail, por exemplo) e execute os comandos abaixo:
|
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 |
Após alguns minutos, o seu log do SQL Server provavelmente já estará assim:
Corrigindo o problema
Agora que entendemos a situação que está ocorrendo e identificamos que esses erros gerados no log do SQL Server são devidos ao usuário ##MS_PolicyEventProcessingLogin## estar órfão, vamos corrigir esse problema.
Para isso, basta associar o login ao usuário, com o comando abaixo:
|
1 2 3 4 5 |
USE [msdb] GO ALTER USER [##MS_PolicyEventProcessingLogin##] WITH LOGIN = [##MS_PolicyEventProcessingLogin##] GO |
Você também pode utilizar a sp_change_users_login para corrigir os usuários órfãos:
|
1 2 3 4 5 |
USE [msdb] GO sys.sp_change_users_login 'Auto_Fix', '##MS_PolicyEventProcessingLogin##' GO |
Resultado:
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.
Observação: Prefiro utilizar o ALTER USER, pois a sp_change_users_login está marcada como deprecated e pode ser removida em versões futuras do SQL Server.
Após realizar essas alterações, as mensagens devem ter parado de ocorrer no log do SQL Server. Como sou teimoso, quero me certificar que está realmente funcionando ao invés de esperar pararem as mensagens de erro:
Agora sim, fiquei tranquilo. 🙂
Espero que tenham gostado desse artigo e até a próxima!
Grande abraço!
Obrigado. Me ajudou!