Hi @vvmichal!

As stated in the documentation here, SELECT is necessary for the batched mode of the sink because it uses SqlBulkCopy. It seems that SqlBulkCopy requires SELECT permission to work.

Form a security perspective I would recommend to use a separate database which is used exclusively for logging. When setting things up like this, the permissions for the identity (user) used by the MSSQL sink to access the DB can be scoped only to that DB and there would be no risk that the SELECT permission leads to any data which could be read by the logging app which is not log data. Even if no separate DB is used for logging, the SELECT permission can also be set exclusively on the log table (or tables). This will ensure that no other tables can be read and minimize the risk of any undesired data visibility to the logging app. This patterns is also recommended by our documentation.

Just to add one more solution in the mix: we are using row level security based on Windows accounts to prevent users from reading other user's logs written from desktop app with integrated auth.

Thank you for your detailed response and for suggesting Row-Level Security (RLS). While RLS could work, I believe requiring each user to be authenticated through Windows or Active Directory (such as with integrated security) may not be ideal for many scenarios.

I still see the need for SELECT permissions as a security concern. While I understand that many may not emphasize this, I’d like to stress that this approach might not align with the best security practices in environments where access should be as minimal as possible.

Here are a few thoughts that come to mind as potential alternatives:

1. Encrypted logs: Although encryption doesn’t fully solve the issue, it adds an additional layer of security, which might be better than exposing SELECT permissions by default.

2. Bulk-less version with INSERT only: Could there be a version of the sink that avoids using SqlBulkCopy and allows for simple INSERT operations without needing SELECT? This could be a better fit for systems where SELECT is a security concern but bulk operations aren't critical.

3. Stored procedure for bulk operations: Would it be possible to handle the bulk insert logic inside a stored procedure that requires only EXECUTE permissions? This way, the user can still take advantage of bulk operations without requiring SELECT or direct INSERT on the table.

Thanks for your time! I’m looking forward to your thoughts.