I make an extensive use of SQL Server Service Broker to trigger/parallelize data science tasks (data imports with SSIS or direct queries to another database on the same server, then data management and machine learning with R and/or Python external scripts).
I wanted to upgrade from SQL Server 2016 to SQL Server 2017, exact same architecture and scripts, but now I get errors I don't understand, apparently caused by the service broker impersonations and privileges.
The activated proc '[StoredProcTriggerdByServiceBroker]' running on queue 'queuename' output the following: 'The server principal "sbuser" is not able to access the database "mainDB" under the current security context.'
To explain a bit a simplification of my process:
There are two DBs,
testDB. ServiceBroker and stored procedures are running on
We mainly use Windows logins to use SQL Server and that was less than ideal for some SSIS purposes, so I chose to create a user called
sbuser for which connection is disabled but which we can impersonate, with sufficient privileges on both DBs to do everything required. The queues are triggering the stored procedure as
The stored procedure fails on statement
SELECT * INTO testDB..targetTable from mainDB..sourceTable
Same select query, impersonating
sbuser manually works fine.
Same select query, in a stored procedure, with a
execute as sbuser /
exec StoredProcedure works fine.
But it fails whenever the stored procedures are triggered by Service Broker.
I added to the stored procedure a logging of
CURRENT_USER and results of
SELECT * into tmp_session FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
CURRENT_USER give me
sbuser, which is what I expect, but the content of
dm_exec_sessions puzzles me as it's different from what I get when I log in as
SA, then execute the procedure impersonating
sbuser with an
sbuser... then again the error message in the log is about sbuser's security context...
So, what has changed between SQL Server 2016 and 2017 in the way the Service Broker works and impersonation of logins and their privileges, and how can I run my
select into query now?