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][1] 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


  [1]: https://stackoverflow.com/help/mcve