We have queue on Service Broker:
ALTER QUEUE [dbo].[my_queue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[my_detect_event] , MAX_QUEUE_READERS = 1 , EXECUTE AS N'dbo' ), POISON_MESSAGE_HANDLING (STATUS = OFF)
Procedure has code (i cut some text with RECEIVE, because it's not linked to the issue):
ALTER PROCEDURE dbo.my_detect_event AS BEGIN ......cut text with RECEIVE TOP (1) message_body FROM my_queue INTO @NotificationStore --EXECUTE AS LOGIN = 'user_sa' INSERT INTO dbo.log ( time, text) VALUES ( GETDATE(), 'test' ) ...... END
user_sa - has SA rights. If the service fires an event, then the table data is inserted successfully. But if you uncomment the line EXECUTE AS LOGIN = 'user_sa', then appears the error:
Cannot execute as the server principal because the principal "user_sa" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Pls help to find solutions. Thanks in advance!
Technical info: Microsoft SQL Server 2012 - 11.0.5582.0 (X64) Enterprise Edition