Como encontrar quando o procedimento armazenado foi excluído e quem o excluiu?

Question

Na verdade, um dos trabalhos críticos falhou durante a execução.

Na mensagem de erro, foi descoberto que a falha ocorreu porque estava faltando um procedimento armazenado.

Agora, como descubro quando o procedimento armazenado foi afetado pelo usuário? Como faço para descobrir qual usuário fez e quando ele fez isso?

    

Answer 1

Você recebe o rastreamento administrativo:

select * from fn_trace_getinfo(NULL)
where property=2
and traceid = 1

O que você procura no rastreamento administrativo para eventos da classe 47 Objeto: Classe de Eventos Excluídos em tipos de objeto Procedimento armazenado do 8727 :

select * from fn_trace_gettable('....trc', -1)
where EventClass = 47
and ObjectType=8727

O rastreamento administrativo é reciclado periodicamente e cerca de 4-5 traços são mantidos, você deve usar o nome do arquivo trc mais antigo que ainda está presente.

Se o procedimento for crítico, o DBA deve certificar-se de que apenas pessoas autorizadas possam modificá-lo ou descartá-lo. E deveria haver auditoria de alterações de esquema. Isso não é culpa de quem quer que tenha desistido do procedimento, mas inteiramente da falha do DBA.

    

Answer 2

Não há como descobrir essas informações por padrão porque o SQL Server não oferece suporte a isso. Se o banco de dados estiver no modo de recuperação total, você poderá tentar ler o log de transações e verificar quando a instrução DROP PROCEDURE foi executada. Infelizmente não há maneira fácil de fazer isso.

Você pode tentar usar algumas das ferramentas de terceiros, como Log ApexSQL ou Quest Toad mas mesmo com essas ferramentas, não tenho certeza se você conseguirá descobrir o nome de usuário de quem fez isso.

Outra opção que você pode tentar é verificar a função fn_dblog e ver se você pode fazer uso disso. Problema aqui é que essa função não está bem documentada.

    

Answer 3

Acho que você está fazendo a pergunta errada aqui.

Por que as pessoas em quem você não está confiando recebem privilégios suficientes em seu banco de dados para realmente deletar o sproc? Essa é a pergunta que você precisa fazer.

É como tentar descobrir quem roubou sua casa depois que você deixou a chave na varanda.

    

Answer 4

Assumindo uma instalação padrão "média" do SQL Server, sentado no seu servidor agora, você não poderá determinar essas informações. Por padrão, o SQL não registra ou rastreia esse tipo de atividade.

(Existem várias maneiras de registrar essas informações (gatilhos DDL), mas isso não ajuda você agora - só ajudaria em atividades futuras.)

Chris mencionou a revisão do log de transações e a extração de quais informações estão presentes. Isso funcionaria, mas o SQL 2005 não fornece nenhuma funcionalidade "nativa" para examinar logs de transações. Você precisaria de uma ferramenta de terceiros para fazer isso. E isso só se aplica enquanto esses dados estiverem no log de transações; se o modo de recuperação do banco de dados estiver definido como "simples", os dados serão apagados do log - mais cedo ou mais tarde. (Se o seu banco de dados é usado ativamente, ele pode já ter desaparecido).

Remus Rusanu descreveu como consultar o rastreio do sistema. Muito legal, eu estou votando nisso! Como ele disse, isso também tem uma vida útil limitada - você provavelmente deve fazer cópias desses arquivos agora antes de serem sobrescritos.

Se as táticas acima não forem possíveis, a restauração e a revisão de backups podem ser rastreadas quando ocorrerem. Isso depende novamente do seu modo de recuperação e dos arquivos de backup que você possui. Se você puder fazer recuperações pontuais em backups de log de transações, será possível obter uma estimativa bastante aproximada sobre quando foi descartada; Se você tiver apenas backups completos ou diferenciais, obterá menos precisão (por exemplo, no backup de 13:00, não no backup de 14:00, deve ter sido descartado entre 1 e 2).

Quanto a quem o descartou (ou melhor, por meio do qual foi feito o login do SQL), a menos que você tenha instalado e executando algum processo intencionalmente configurado, não acredito que possa extrair essa informação. Um ponto de partida seria determinar quem (em vez disso, quais logins) poderiam executar a queda e ir a partir daí. Sua instalação do SQL está configurada para registrar logins bem-sucedidos nos logs de eventos do Windows? O domínio está definido para rastrear logins de domínio? ... embora nenhum dos dois ajude se a autenticação do SQL estiver envolvida.

Pode não ser possível, mas você pode conseguir algumas suposições razoáveis. Boa sorte!

    

Answer 5

Você deve poder voltar por um log de transações e saber ao menos quando esse procedimento foi removido do banco de dados. Quanto a quem eram os usuários, você pode ver quem estava conectado ao sistema naquele momento e conseguir reduzi-lo um pouco.

Espero que isso ajude alguns.

    

Answer 6

Você pode usar o script abaixo para identificar um usuário suspeito:

SELECT 
    te.name AS eventtype
    ,t.loginname
    ,t.spid
    ,t.starttime
    ,t.objectname
    ,t.databasename
    ,t.hostname
    ,t.ntusername
    ,t.ntdomainname
    ,t.clientprocessid
    ,t.applicationname  
FROM sys.fn_trace_gettable
(
    CONVERT
    (VARCHAR(150)
    ,(
        SELECT TOP 1 
            value
        FROM sys.fn_trace_getinfo(NULL)  
        WHERE property = 2
    )),DEFAULT
) T 
INNER JOIN sys.trace_events as te 
    ON t.eventclass = te.trace_event_id 
WHERE eventclass=164