SQLTwins by Nakul Vachhrajani

Home | About Nakul Vachhrajani | Archives

Enabling T-SQL Debugger in SQL Server Management Studio (SSMS)

May 16, 2011 9:00 AM

I hope that all of you had a chance to participate in my session on the “Underappreciated Features of Microsoft SQL Server” during the Exclusive SQL Server Webcast series on Security & Scalability. If not, I trust the recorded sessions would be up soon for you to see. I will share the location of the same once available.

The session went very smooth, and I was fortunate enough to have a strong following throughout the session. I presented the following underappreciated features during the session:

For Administration: Object Explorer Details and Object Explorer
For Development: Template Explorer and T-SQL Debugger

Looking at the number of questions after the session during the Q&A and from my colleagues at the office, it looks like I underappreciated the uniqueness of the feature – the T-SQL debugger. I received a number of questions related to the debugger, and I will attempt to address a few of these in the upcoming sessions.

For those who would like a quick, hands-on experience of the T-SQL Debugger, you may want to visit my article at: http://beyondrelational.com/blogs/nakul/archive/2011/02/02/t-sql-debugger-underappreciated-features-of-microsoft-sql-server.aspx

Some of the questions I received were:

Is the T-SQL Debugger version or edition specific?
I use SQL Server 2005 and I am not able to see the debugger
I use SQL Server 2008 R2, and yet the debugger is not available

The answer to each of these questions is as follows:

History

The T-SQL Debugger has been around at least since the days of Microsoft SQL Server 2000 (that’s when I started using it) – it’s just that it’s not being used often which makes it an underappreciated feature, and one of the key demos that I wanted to demonstrate in the session.

Enabling the T-SQL Debugger in SQL Server Management Studio

To the best of my knowledge, the debugger is available in all editions of the Microsoft SQL Server, except the Express edition. Hence, if you are not able to see the T-SQL Debugger in your SSMS, most probably, you are using the SSMS that comes with the Express edition.

If you can see the debugger, but are not able to debug a T-SQL query, it is important to note that not everyone is allowed to debug T-SQL code. This is because, T-SQL Debugging is a highly-privileged operation. To enable the T-SQL debugger in SSMS, the following must be true:

The user context under which the SSMS is running must be a member of the sysadmin fixed server role
The user must be connected to the SQL Server using a user who is member of the sysadmin fixed server role

Until we meet next time,

Be courteous. Drive responsibly.