5

BOL documentation says sysadmin permission is required to run debugger.

I'm 90% certain there is no workaround to this requirement, but thought I would ask just in case someone found a way to grant Debugger permission without granting sysadmin permission.

What do people do when you have a team of developers needing to step through a complicated cursor loop with variables, etc to debug some aspect of that?

Most shops don't allow developers to have sysadmin permission even on development servers, and many wouldn't allow devs to keep a copy of enterprise data on their local machine with their own developer sql server edition e.g. due to PII and data security reasons.

Not sure why the debugger would be set up this way.

So, I'm curious how other people handle the requests for Debugger permission in a similar scenario.

What do you do in your environment?

  • 3
    Lots of PRINT statements... ;) – Erik Darling Oct 12 '17 at 18:52
  • I was afraid of this. In our scenario, the loop/cursor, has lots of control variables, and touches data in many tables... it's almost not feasible to debug it any other way than to use the debugger. – GWR Oct 12 '17 at 18:59
  • @GWR I'd have to say that's exactly why it does require sysadmin privileges. Running in debugging mode is an elevated process. If you don't have that check, you allow someone to do something they normally aren't able to do. – mathewb Oct 12 '17 at 19:40
  • 1
    Yes I get it... Why exactly does the debugger use an elevated process? Really the dev just wants to step through code he can execute on his own any way, and inspect the data and variables etc at each point. He can do this by tediously using print statemetns, but it takes 20x longer this way. Too bad there wasn't a pared down version of the debugger to allow at least ability to step through, and look at variable values, inspect the rows involved, etc... Seems like all this printing etc., is re-inventing a worse version of the wheel. – GWR Oct 12 '17 at 19:57
  • 1
    @GWR A couple of aspects that stand out to me are that locks are still in effect, so you don't want someone hosing a system because they want to debug in production, and also you are allowed to modify values in your watch list, which is where you can start to get into some serious trouble by playing around with what is happening. My suggestion below is specifically for if you want to allow use of the debugger. I use print or select a lot for debugging myself. BEGIN ... ROLLBACK is also my friend when doing so. – mathewb Oct 13 '17 at 3:55
4

You could add a declare @idebug int variable to your stored procedures and then code around the important bits when you require relevant information.

Your stored procedure would then look a bit like this:

CREATE PROCEDURE [dbo].[uspDoSomething]
...
@iiDebug int = 0
...
AS
...
BEGIN
    /* debugging configuration */   
    declare @debug int

    /* debug settings
    1 = turn on debug information
    2 = turn on all possible outputs
    4 = turn on transaction handling

    e.g.: Adding an @iDebug paramter of 6 will turn on transaction handling 
    and turn on all possible output information

    e.g.: Adding an @iDebug value of 1 will turn on debugging information
    */ 

    set @debug = @iiDebug
    ....
    if @debug & 1 = 1  print 'Checking variables...'

                    /* If general output has been turned on print output*/ 
                    if @debug & 2 = 2 
                        BEGIN
                            PRINT 'Debug comment here' + convert(varchar(100), @iRetVal) + 'Debug comment here' + convert(varchar(20),getdate())
                        end

                    close <cursor_name>
                    deallocate <cursor_name>

                    RETURN(@iRetVal)
    ...
    END
    ...
END

This is just an example of how it can be done.

You would then call the sproc with:

execute uspDoSomething @iiDebug = 3

...which would the provide basic (bitwise 1) and detailed (bitwise 2) information, depending on where you inserted the relevant code.

I had issues once while running a stored procedure that wasn't producing the right results and I had to debug the individual statements, so I just entered the various debugging levels in the stored procedure and when required ran the sproc with the relevant @iiDebug values depending on the level of information I required.

Examples of input values:

@iiDebug = 1 -- > Basic "where am I in the sproc" information
@iiDebug = 2 -- > Print of @nvSQL values
@iiDebug = 4 -- > Run individual execution of statements in BEGIN and COMMIT transactions

Examples as code (input variable @iiDebug is stored in @debug in the sproc code):

set @debug = @iiDebug
...
...
if @debug & 4 = 4
BEGIN
    begin tran mojo
END
if @debug & 2 = 2 then print @nvSQL

exec @iRetVal = sp_executesql @nvSQL
if @iRetVal <> 0 
BEGIN
    /* If transactions have been turned on then rollback if failed */
    if @debug & 4 = 4
    BEGIN
        rollback tran mojo
    END

/* If transactions have been turned on then commit on success */
if @debug & 4 = 4
BEGIN
    commit tran mojo
END

These are just quick examples of how you can introduce debugging without having access to the SQL Server Debugger or the required privileges.

Caution:
It can be a bit of a performance hog and is better removed from production.

  • I definitely do this type of thing when I am dealing with smaller sets of code, couple hundred lines or something... but in many cases, the need is to support devs who have to troubleshoot inherited t-sql code with cursor loops, nested a number of levels, with many control variables, all spanning thousands of lines of code... so as you might imagine, this approach doesn't scale well to that type of work – GWR Oct 13 '17 at 15:11
1

If you're not totally opposed to giving each developer a copy of the database to play with locally, then consider taking the time to setup a script in your test/dev environment that will pare the database down to a toy size. Keep the last n months of data, scrub/anonymize your PII data, and as much as it pains me to say it, if necessary, set up the data needed to test the scenarios (DBAs, well, at least I, don't like setting up test data for other developers, but if you're the only one with access to it, then I guess you have to). Each developer can then take that database and restore it locally.

Where I work, we have a sandbox server where anything goes, so it's optional for the developer to restore locally or play in the sandbox. But the same concept as above applies.

1

Not sure why the debugger would be set up this way.

Most likely due to the invasive / insecure nature of debugging: the debugger attaches to the SQL Server process itself so that it can see inside that process and even make on-the-fly changes. That's taking control of the server. And greatly impacts security as well as stability.

This is also why you should not be doing this in Production. At all.

What do people do when you have a team of developers needing to step through a complicated cursor loop with variables, etc to debug some aspect of that?

Get new / better developers ;-)

and many wouldn't allow devs to keep a copy of enterprise data on their local machine with their own developer sql server edition e.g. due to PII and data security reasons.

True. This is why I have always found it helpful to set up an isolated testing machine. Whatever data from Production can be loaded onto it but it only needs to contain enough of the data to step through the code being debugged. It could even be isolated so that whoever is using it can't transfer the data down to their dev machine. But the code can be modified in place without needing to through code review since it is just testing / debugging. And you don't need to worry about data modification, same as if they would have restored this data on the developer's workstation.

Once the issue has been identified and a fix tested, the database(s) as well as data and log file(s) can be dropped. If there is PII that needs to be scrubbed (assuming doing so wouldn't interfere with testing / debugging), then that can be done before giving access to any developer.

0

I use SQL Builder from CAST (ver 7.0.11, build 4230). It allows me to debug stored procedures WITHOUT these special permissions that SQL Server Management Studio requires. I did have to create a dummy stored procedure inside own database for SQL Builder to work:

Create Proc sp_dboption (
      @dbname varchar(30) = NULL,
      @optname varchar(20) = NULL,
      @optvalue varchar(10) = NULL,
      @dockpt tinyint = 1)
As
Begin
    return(0)
end

The only problem is that the CAST tools are not free.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.