SQL Server 2005 Debugging Requirements

Interesting Finds: July 8, 2006

Jason Haley — Sat, 08 Jul 2006 18:32:22 GMT

re: SQL Server 2005 Debugging Requirements

Clifton Collins — Mon, 10 Jul 2006 17:02:08 GMT

I think MS is on the fence with debugging and has continuity issues larger than just the SQL product. Why do similar things differently, develop one way, debug one way, etc. That is what is touted by the MS propaganda isn't it. The more years pass, the more it stays the same, but with a different look and is not getting better, just different. This may be the continuity MS wants, but not developers and users.

...And make sure you take away important features like tsql debugging from the sql product and the sql agent job scheduler from express. I am sure this makes sense to someone that has never used the product.

re: SQL Server 2005 Debugging Requirements

Andy Ball — Wed, 12 Jul 2006 12:18:55 GMT

Firstly thanks for a really useful article and a great blog. I will be adding you to my RSS feed.

This is a real issue in any decent sized corporate environment. Even dev servers will be standardised and well managed and therefore Sa access will be restricted to the DBA Team. I understood that one of the main benefits of VS2005 is the seamless debug between managed code and SQL and in our environment the Sa requirement prevents us leveraging this.

Obviously there are workarounds (ie temp grant access, local / SQL Express copy of SQL Server) but these create a manageability headache which again makes them unworkable in a Corporate environment.

We still see quite a few examples in MS Products of ignorance of corporate realties , so it would be good if these are thought through / discussed with your larger customers more. I'm happy to do this over a Red Rock / Pyramid or two :-)

thanks again,
Andy.

re: SQL Server 2005 Debugging Requirements

Dean Harding — Thu, 13 Jul 2006 05:55:14 GMT

The problem with debugging is that you really DO have to have access to the process's memory to be able to do anything useful. And if they added some new "debugging" role to allow people to debug, it'd be the same as the sysadmin role anyway (at least, it would be possible for a "debugging" user to trick the server into thinking they were a "sysadmin"). Essentially, the "debugging" role would be synonymous with the sysadmin role.

At least requiring the sysadmin role makes it very clear to DBAs the sort of permissions you're giving developers. This'll make it clear that they probably should NOT be giving it to developers on a production server. If they had available some "debugging" role, they might be tempted to think "Oh, it's just debugging, that's not so bad" when in fact it really is.

re: SQL Server 2005 Debugging Requirements

Vineet.Rao (MSFT) — Thu, 13 Jul 2006 22:11:08 GMT

What Dean has described is absolutely correct. While debugging you are allowed to perform a number of high privileged operations that are sysadmin level. For e.g. you can change the behavior of the application by modifying parameter values during T-SQL debugging (if the parameter is dynamic sql you can change it and execute anything).
Microsoft can either limit the kind of things you can do during debugging and thus allow a more granular permissioning model or be rich in debugging features and be tight on who can debug.
Andy, feel free to contact us with your requirements and we'll what we can do in future releases.

Thanks,
Vineet Rao
Program Manager
Microsoft SQL Server

re: SQL Server 2005 Debugging Requirements

Andy Ball — Fri, 14 Jul 2006 12:01:44 GMT

Chaps,
thanks a lot for posting back.

Dean - you mention a debugging role as if its a mythical thing. SQL Server 2000 had the sp_sdidebug stored procedure where you could grant permissions to users / developers.

I understand the security concern / access to memory point , but in reality average developer won't be figuring out how to hack sql server so he gets Sa access.

Our requirements are that developers should be able to debug TSQL on our development servers. These are well managed, standardised and therefore Local Admins \Sa access is forbidden.

In Development environment , Developers will tend to have just dbo.

I totally agree the point that granting Developers access to debugging on a Prod Server is a bad idea. We would only do this in very rare cases when an App Developer is 3rd / 4th Line support and requires debugging to troubleshoot a Production problem. This access would be temporary / managed under emergency control.

The infrastructure / Dev setup I describe is pretty much standard at the 5 or 6 Financial Institutions I've worked here in the UK.

thanks again,
Andy.

re: SQL Server 2005 Debugging Requirements

prabhupr — Fri, 14 Jul 2006 13:55:02 GMT

Adding a VISIO diagram explaining this would be of great help too.

re: SQL Server 2005 Debugging Requirements

pvmurty — Sat, 29 Jul 2006 20:47:43 GMT

great help for sysadmin

re: SQL Server 2005 Debugging Requirements

James Bennett — Wed, 23 Aug 2006 17:41:08 GMT

Nope SQL debugging has once again failed for me. I set up as described here. Sometimes it works and sometimes it doesn't. Doesn't matter that I haven't changed any settings. For the last few weeks no matter what I do it just refuses to work. Just make it reliable. The rest I don't care about. Hell all of your tools, just make them reliable. I'm fed up with using a tool one day and it doing one thing and then another day it doing another. Just make them always work in the same way, or if it is going to error at least make sure the error message has some relevance to the error that caused the fault. Oh I give up. No-one's interested in my rants (I know I'm not). I'll just go and debug this SP by hand, It will actually be quicker than the amount of time I have once again spent trying to get T-SQL debugging working.

Debugging stored procedures on SQL Server 2005

jokiz — Wed, 26 Sep 2007 06:43:02 GMT

My current task in our project is majorly in MSSQL. I wanted to debug a chain of stored procedures and

T-SQL Debugger

Troubleshooting and Tips - Cindy Gross — Tue, 24 Jun 2008 02:20:28 GMT

<p>Many people have asked where the TSQL debugger is for SQL Server 2005.</p> ...