I've been trying to get this Service Broker and timers thing to work for over a week straight now, so bear with me if this comes off as.. uh.. ragey. I'm also fairly inexperienced when it comes to SQL Server.

I've set up two databases to handle a very large amount of data. The first database is used for staging, the tables have no referential integrity and my application will pound these tables just to get the data into the database. That works great. The second database is for production-ready data, has the integrity constraints etc... I've set up synonyms that point to Production's tables from Staging.

I've set up a Service Broker timer queue/service to execute every few seconds to migrate data from completed staging tables into production. The SQL for that is as follows:

IF EXISTS (SELECT * FROM sys.services WHERE name = 'My_MigrationService')
BEGIN 
    DROP SERVICE My_MigrationService
END 
GO

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'My_MigrationQueue')
BEGIN 
    DROP QUEUE My_MigrationQueue
END 
GO

CREATE QUEUE My_MigrationQueue
GO

CREATE SERVICE My_MigrationService ON QUEUE My_MigrationQueue ([DEFAULT])
GO

ALTER QUEUE My_MigrationQueue 
WITH ACTIVATION (
    STATUS = ON
    , MAX_QUEUE_READERS = 1
    , EXECUTE AS OWNER
    , PROCEDURE_NAME = migration_handler
);
GO


IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'restart_migration_conversation')
BEGIN 
    DROP PROCEDURE [dbo].[restart_migration_conversation]
END 
GO


CREATE PROCEDURE restart_migration_conversation
AS 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
    DECLARE @conversationHandle UNIQUEIDENTIFIER = (SELECT TOP 1 [conversation_handle] 
                                                    FROM sys.conversation_endpoints
                                                    WHERE [far_service] = 'My_MigrationService')

    IF @conversationHandle IS NOT NULL
    BEGIN 
        END CONVERSATION (@conversationHandle)
    END 
    
    BEGIN DIALOG CONVERSATION @conversationHandle
        FROM SERVICE [My_MigrationService]
        TO SERVICE N'My_MigrationService', N'CURRENT DATABASE'
        WITH ENCRYPTION = OFF;

    BEGIN CONVERSATION TIMER (@conversationHandle) TIMEOUT = 1;
    
END
GO



CREATE PROCEDURE migration_handler
AS 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT On;
    DECLARE @messageType        SYSNAME
    DECLARE @conversationHandle UNIQUEIDENTIFIER
    BEGIN TRANSACTION;
        RECEIVE TOP (1)
            @messageType = [message_type_name]
            , @conversationHandle = [conversation_handle]
        FROM My_MigrationQueue
        
        IF @conversationHandle IS NOT NULL AND @messageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer'
        BEGIN
            EXEC migrate_staging_data
            
            BEGIN CONVERSATION TIMER (@conversationHandle) TIMEOUT = 2;
        END

    COMMIT TRANSACTION;
END
GO

The procedure migrate_staging_data is the one that contains the synonym usage to the Production database. I'm excluding it because it's very large and generating a MCVE will be time consuming for not much gain.

The queue executes properly, on time etc... Now, when I execute migrate_staging_data by using SSMS everything goes according to plan, the data is migrated properly and everything is great. As soon as I execute it with the Service Broker timer I get an entry in my SQL Server logs that says

The activated proc '[dbo].[migration_handler]' running on queue 'Staging.dbo.My_MigrationQueue' output the following: 'The server principal "sa" is not able to access the database "Production" under the current security context.

And subsequent attempts through the Broker to call migration_handler outputs

The activated proc '[dbo].[migration_handler]' running on queue 'Staging.dbo.My_MigrationQueue' output the following: 'The service queue "My_MigrationQueue" is currently disabled.'

Why?

Why does sa not have permissions under the Broker like it would while executing from SSMS or another context? How can I give sa permission to do this?

I've tried adding 'WITH EXECUTE AS SELF' TO the migrate_staging_data procedure based on some research, to no avail. I've also tried changing the ALTER QUEUE My_MigrationQueue statement to include EXECUTE AS SELF

Basically all I want to do is execute my migration procedure every x seconds from within SQL Server. Is there a better way without the Broker to just execute a procedure every once in a while?

Just to note: I don't plan on using the sa login in actual production, I will create new logins for the databases when the time comes.

I'm using SQL Server Enterprise x64, version 12.0.4100.1