patternsqlMinor

SQL Server Debugger Permissions without Granting sysadmin to Devs

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

devswithoutpermissionssqlgrantingsysadminserverdebugger

Problem

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?

Solution

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 
                    deallocate 

                    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.

Code Snippets

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

execute uspDoSomething @iiDebug = 3

@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

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

Context

StackExchange Database Administrators Q#188348, answer score: 4

Revisions (0)

No revisions yet.