Version 7.x of the transport requires SELECT permission, it was not the case for previous versions

[mauroservienti]

Describe the bug

Description

As reported in the public discussion forum it seems that version 7.x of the transport requires SELECT permission. it's needed at startup to check the presence of the Recovarable column:

NServiceBus.SqlServer/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs

Lines 148 to 161 in f7c487d

	var commandText = Format(SqlConstants.CheckIfTableHasRecoverableText, qualifiedTableName);
	using (var command = new SqlCommand(commandText, connection, transaction))
	{
	using (var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
	{
	for (int fieldIndex = 0; fieldIndex < reader.FieldCount; fieldIndex++)
	{
	if (string.Equals("Recoverable", reader.GetName(fieldIndex), StringComparison.OrdinalIgnoreCase))
	{
	cachedSendCommand = Format(SqlConstants.SendTextWithRecoverable, qualifiedTableName);
	return cachedSendCommand;
	}
	}
	}

Expected behavior

No need for the SELECT permission to send messages.

Actual behavior

Versions

Please list the version of the relevant packages or applications in which the bug exists.

Steps to reproduce

Relevant log output

No response

Additional Information

Workarounds

Possible solutions

As mentioned in https://discuss.particular.net/t/nservicebus-8-needs-select-permission-to-send-messages/3496/2 use SELECT * FROM SYS.COLUMNS that doesn't require the SELECT permission

Additional information

[MarcWils]

Any update on this issue? We're about to update a lot of endpoints to version 7 of the transport. We have thight security measures in place. It would be nice if we could keep away from granting SELECT permissions for sending messages.

[SzymonPobiega]

I marked it as a candidate for the next release but we can't at this point say when that release it going to happen.