sa doesn't have permissions to other database through synonyms with the Service Broker

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

The easiest (quick and dirty solution) but risky in terms of security approach would be to mark the database as TRUSTWORTHY using ALTER DATABASE [your_db_name] SET TRUSTWORTHY ON.

Remember that marking the database trustworthy elevates the database dbo to a de-facto sysadmin

You are getting below error :


'The server principal "sa" is not able to access the database "Production" under the current security context.

because the activation execution context is trusted only in the database, not in the whole server. Anything related to the whole server, like a server level view or a dynamic management view or a linked server, acts as if you logged in as [Public].

Refer thoroughly blog post by Remus Rusanu :

• Call a procedure in another database from an activated procedure

• Extending Database Impersonation by Using EXECUTE AS

Also, you should have process in-place to Handle Poison Messages to prevent disabling the queue.


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?

You can look into approach like incremental load using SSIS from staging to PROD database. You can apply different techniques when loading the data as well.

StackExchange Database Administrators Q#122216, answer score: 3