We've been trying to set-up SQL debugger for our SQL Server database instance on test environment. Our aim was to allow developers to debug SQL Server stored procedure execution through Visual Studio.
One of the requirements is to grant sysadmin role for the user. We created a dedicated SQL Server user for this purpose and we pass its credentials when starting debugger, but unfortunately it does not work. It appears that the SQL Server user is ignored by the debugger and it is Windows user that runs Visual Studio that is passed to the database (and that user is missing sysadmin on the SQL Server of course).
In details here is what we do:
- First we run Visual Studio (2019, 2022 - it makes no difference) as a current Windows user and prepare some script that runs a stored procedure in SQL Server. We run it using 'Execute with debugger' option
- SQL Server connection dialog appears where we enter the SQL Server user with sysadmin role in SQL Server:
- After clicking 'Connect', the following error appears:
We already checked that granting sysadmin role for the Windows user that runs Visual Studio resolves the issue but this is not the way to go.
Do you have any idea if it is some bug in the debugger or the behaviour is as it should be? I do not get the purpose of displaying SQL Server connection dialog if it is ignored eventually.
Thanks.