I want to debug in TSQL.When I start the debug the following message box popup. In SSMS v17.2
Then I see this message:
In Microsoft Visual Studio 2015 Server Explorer same Error:
Then I see this message:
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Debugging is a highly privileged operation. Therefore, only members of the sysadmin fixed server role are allowed to debug in SQL Server.
See How to: Enable Transact-SQL Debugging for details on the requirements.
There also several Connect items discussing this and listing some possible scenarios:
While I understand the frustration behind lack of debugging support for non-sysadmin members, there are lot of challenges in enabling such support in the engine. We looked at in the past & didn't find a clean way to isolate the debugging experience in the server without affecting other processes. Below are some key issues:
- Debugging in general requires higher privileges, this is true of SQL Server or OS for that matter
- Debugging SQLCLR code in SQL Server for example can freeze other SQLCLR tasks in the server severely affecting performance
- Debugging endpoints are DCOM based today and it has its own restrictions
- Lastly, debugging T-SQL code in a production server is not something we recommend. The scenario is primarily for development purposes and there are alternatives which I will describe below
You can use Visual Studio or SQL Server Management Studio or SQL Server Data Tools (SSDT)to debug against a local copy of SQL Server. In SQL Server 2012, we also have the SQL Server Express LocalDB SKU which is used by SSDT. Today, the surface area / features supported in LocalDB is a subset of SQL Server but our long-term goal is to enable all features that are supported in SQL Server including SQL Azure. So our recommendation is to use the local dev environment to debug the T-SQL logic. Additionally, you can obtain clone database (which includes statistics without data) to repro query plan related issues too. This will work for most cases other than environment specific problems or missing features in LocalDB. Hope this helps.
Umachandar, SQL Programmability Team
The minimum security requirement is sysadmin. This is by design from SQL engine security.