3

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, mainDB and testDB. ServiceBroker and stored procedures are running on testDB.

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 sbuser.

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 execute as.

security_id
0x01
login_name
sa
context_info
0x
original_security_id
0x01
original_login_name
sa

Nothing about 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?

Your Answer

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

Browse other questions tagged or ask your own question.