SQL Server Debugger Permissions without Granting sysadmin to Devs

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.