Service Broker – Using Procedures with Execute As

service-broker

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

Best Answer

Answer is here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/95f5ee43-d3b2-4a2d-bdd2-6fb0aed56458/service-broker-and-procedure-which-contains-execute-as?forum=sqlservicebroker&prof=required In short: I perform in the context of the current database and treated in a nearby base. And it requires action from the links above.

Related Question