Permissões do depurador do SQL Server sem conceder sysadmin aos desenvolvedores

Você pode adicionar uma declare @idebug intvariável aos seus procedimentos armazenados e, em seguida, codificar os bits importantes quando precisar de informações relevantes.

Seu procedimento armazenado ficaria um pouco assim:

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

Este é apenas um exemplo de como isso pode ser feito.

Você então chamaria o sproc com:

execute uspDoSomething @iiDebug = 3

...que forneceria informações básicas (bit a bit 1) e detalhadas (bit a bit 2), dependendo de onde você inseriu o código relevante.

Eu tive problemas uma vez ao executar um procedimento armazenado que não estava produzindo os resultados corretos e tive que depurar as instruções individuais, então acabei de inserir os vários níveis de depuração no procedimento armazenado e, quando necessário, executei o sproc com os @iiDebugvalores relevantes dependendo de o nível de informação que eu precisava.

Exemplos de valores de entrada:

@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

Exemplos como código (a variável de entrada @iiDebugé armazenada no @debugcódigo sproc):

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

Estes são apenas exemplos rápidos de como você pode introduzir a depuração sem ter acesso ao SQL Server Debugger ou aos privilégios necessários.

Cuidado:
pode ser um pouco de desempenho e é melhor removido da produção.

Se você não se opõe totalmente a dar a cada desenvolvedor uma cópia do banco de dados para jogar localmente, considere reservar um tempo para configurar um script em seu ambiente de teste/desenvolvimento que reduzirá o banco de dados a um tamanho de brinquedo. Mantenha os últimos n meses de dados, limpe/anonimize seus dados de PII e, por mais que me doa dizer, se necessário, configure os dados necessários para testar os cenários (DBAs, bem, pelo menos eu, não como configurar dados de teste para outros desenvolvedores, mas se você é o único com acesso a eles, acho que precisa). Cada desenvolvedor pode pegar esse banco de dados e restaurá-lo localmente.

Onde eu trabalho, temos um servidor sandbox onde vale tudo, então é opcional para o desenvolvedor restaurar localmente ou jogar no sandbox. Mas o mesmo conceito acima se aplica.

Não tenho certeza por que o depurador seria configurado dessa maneira.

Muito provavelmente devido à natureza invasiva/insegura da depuração: o depurador se anexa ao próprio processo do SQL Server para que ele possa ver dentro desse processo e até mesmo fazer alterações em tempo real. Isso é assumir o controle do servidor. E impacta muito a segurança, bem como a estabilidade.

É também por isso que você não deve fazer isso na Produção. De forma alguma.

O que as pessoas fazem quando você tem uma equipe de desenvolvedores precisando passar por um loop de cursor complicado com variáveis, etc., para depurar algum aspecto disso?

Obtenha novos/melhores desenvolvedores ;-)

e muitos não permitiriam que os desenvolvedores mantivessem uma cópia dos dados corporativos em sua máquina local com sua própria edição do servidor sql do desenvolvedor, por exemplo, devido a PII e razões de segurança de dados.

Verdadeiro. É por isso que sempre achei útil configurar uma máquina de teste isolada. Quaisquer dados da Produção podem ser carregados nele, mas ele só precisa conter dados suficientes para percorrer o código que está sendo depurado. Ele pode até ser isolado para que quem o estiver usando não possa transferir os dados para sua máquina de desenvolvimento. Mas o código pode ser modificado no local sem a necessidade de revisão de código, pois é apenas teste/depuração. E você não precisa se preocupar com modificação de dados, como se eles tivessem restaurado esses dados na estação de trabalho do desenvolvedor.

Depois que o problema for identificado e uma correção testada, os bancos de dados, bem como os dados e os arquivos de log, poderão ser descartados. Se houver PII que precise ser limpo (supondo que isso não interfira no teste/depuração), isso pode ser feito antes de dar acesso a qualquer desenvolvedor.

Eu uso o SQL Builder do CAST (versão 7.0.11, build 4230). Ele me permite depurar procedimentos armazenados SEM essas permissões especiais que o SQL Server Management Studio requer. Eu tive que criar um procedimento armazenado fictício dentro do próprio banco de dados para o SQL Builder funcionar:

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

O único problema é que as ferramentas CAST não são gratuitas.