NServiceBus 8 needs select permission to send messages

Hello all. We noticed that NSB8 needs select permission for destination queue table when sending message. For NSB7 insert permission was enough. Is it a bug or a feature?
Here is the quote from the doc: “Each endpoint should use a dedicated SQL Server principal with SELECT and DELETE permissions on its input queue tables and INSERT permission on input queue tables of endpoints it sends messages to.”
In any case, since the switch to NSB8, we get following exception:

System.Exception: Failed to send message to [TargetEndpointDB].[TargetEndpointSchema].[TargetEndpointQueue]
 ---> System.Data.SqlClient.SqlException (0x80131904): The SELECT permission was denied on the object 'TargetEndpointQueue', database 'TargetEndpointDB', schema 'TargetEndpointSchema'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()
   at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod)
   at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
   at System.Data.SqlClient.SqlCommand.EndExecuteReader(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at NServiceBus.Transport.SqlServer.TableBasedQueue.GetSendCommandText(SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs:line 151
   at NServiceBus.Transport.SqlServer.TableBasedQueue.SendRawMessage(MessageRow message, SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs:line 104
ClientConnectionId:713f1bf3-e5ad-43e3-ad85-4200f878f5dc
Error Number:229,State:5,Class:14
   --- End of inner exception stack trace ---
   at NServiceBus.Transport.SqlServer.TableBasedQueue.ThrowFailedToSendException(Exception ex) in /_/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs:line 180
   at NServiceBus.Transport.SqlServer.TableBasedQueue.SendRawMessage(MessageRow message, SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs:line 126
   at NServiceBus.Transport.SqlServer.MessageDispatcher.Dispatch(IEnumerable`1 operations, SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Sending/MessageDispatcher.cs:line 147
   at NServiceBus.Transport.SqlServer.MessageDispatcher.DispatchIsolated(SortingResult sortedOperations, TransportTransaction transportTransaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Sending/MessageDispatcher.cs:line 82
   at NServiceBus.Transport.SqlServer.MessageDispatcher.Dispatch(TransportOperations operations, TransportTransaction transportTransaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Sending/MessageDispatcher.cs:line 35
   at NServiceBus.TransportReceiveToPhysicalMessageConnector.Invoke(ITransportReceiveContext context, Func`2 next) in /_/src/NServiceBus.Core/Pipeline/Incoming/TransportReceiveToPhysicalMessageConnector.cs:line 63
   at NServiceBus.RetryAcknowledgementBehavior.Invoke(ITransportReceiveContext context, Func`2 next) in /_/src/NServiceBus.Core/ServicePlatform/Retries/RetryAcknowledgementBehavior.cs:line 25
   at NServiceBus.MainPipelineExecutor.Invoke(MessageContext messageContext, CancellationToken cancellationToken) in /_/src/NServiceBus.Core/Pipeline/MainPipelineExecutor.cs:line 45
   at NServiceBus.MainPipelineExecutor.Invoke(MessageContext messageContext, CancellationToken cancellationToken) in /_/src/NServiceBus.Core/Pipeline/MainPipelineExecutor.cs:line 64
   at NServiceBus.Transport.SqlServer.ProcessStrategy.TryHandleMessage(Message message, TransportTransaction transportTransaction, ContextBag context, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Receiving/ProcessStrategy.cs:line 49
   at NServiceBus.Transport.SqlServer.ProcessWithNativeTransaction.TryProcess(Message message, TransportTransaction transportTransaction, ContextBag context, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Receiving/ProcessWithNativeTransaction.cs:line 112
Exception details:
	Message ID: 3cd75a97-8441-4e9b-8e90-afce00f12919
	Transport message ID: 50213f42-1e8c-4665-ad8e-a0e9667104a3
	Pipeline canceled: False

Hello NSB Team.
Unfortunately no one has responded to my post.
We found out that the SELECT permission is required for checking if the ‘Recovarable’ column is present in the queue table. This is done by SELECT on the queue table followed by a foreach loop. Better would be a SELECT * FROM SYS.COLUMNS. For this it does not need a SELECT permission on the queue table.
Best regards

@dim,

thanks for reporting this and the additional investigation leading to nailing the problem. And please, accept my apologies for not responding to the original message.

It sounds like a bug considering that there is an approach that allows us to avoid the need for the SELECT permission.

Is this a send-only endpoint?

In the meantime, I raised Version 7.x of the transport requires SELECT permission, it was not the case for previous versions · Issue #1196 · Particular/NServiceBus.SqlServer · GitHub

.m

@mauroservienti,

thanks for your answer. Yes, this only concerns sending

Best regards