0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.