Início / dba / Perguntas / 10716
Accepted
xorpower
Asked: 2012-01-17 05:53:17 +0800 CST

Determinando como uma mudança de esquema ocorreu?

  • 772

Algo ruim aconteceu ontem.

Uma visão que foi criada há algum tempo foi modificada por alguém que acabou quebrando os relatórios. Infelizmente. alguém (consciente ou inconscientemente) fez esta modificação no banco de dados PRODUCTION.

Minha pergunta: Existe uma maneira (script/software/freeware etc) pela qual podemos saber quem (nome de usuário) fez essa modificação, para que eu possa revogar o acesso ao banco de dados de produção para esse usuário.

Se minha pergunta não estiver clara, por favor, comente.

sql-server view

4 respostas

  1. Best Answer

    Isso é registrado no rastreamento padrão, portanto, desde que esteja ativado e não tenha sido substituído nesse meio tempo, ele deve aparecer no relatório "Histórico de alterações de esquema".

    Para acessá-lo no Management Studio, clique com o botão direito do mouse no banco de dados e, no menu de contexto, escolhaReports -> Standard Reports -> Schema Changes History

    Para recuperar as mesmas informações via TSQL, você pode usar

    SELECT StartTime
       ,LoginName
       --,f.*
FROM   sys.traces t
       CROSS APPLY fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path),
                                                       CHARINDEX('\', REVERSE(t.path)), 
                                                       260)
                                             ) + N'log.trc', DEFAULT) f
WHERE  t.is_default = 1
       AND ObjectName = 'FOO'
       AND EventClass IN (46, /*Object:Created*/
                          47, /*Object:Dropped*/
                          164 /*Object:Altered*/ )
    • 37

  2. Martin já indicou o melhor caminho, o rastreamento de auditoria administrativa que geralmente está ativado (a menos que tenha sido explicitamente desativado). Se você não conseguir encontrar as informações no rastreamento do administrador (foi desativado ou foi reciclado), você pode recuperar as informações dos backups de log. Como é um banco de dados de produção, suponho que você tenha um ciclo de backup regular, com backup completo periódico e backups de log. Você precisará restaurar, em um servidor separado, o banco de dados na hora do incidente para que o DDL esteja no log restaurado atual. Então é uma simples questão de usar fn_dblog()e inspecionar o log.

    Uma maneira é seguir as operações de início de transação:

    select [Begin Time], [Transaction Name], [Transaction SID], * 
from fn_dblog(null, null)
where Operation = 'LOP_BEGIN_XACT';

    Se o ALTER VIEWfoi emitido em uma transação autônoma (ou seja, não cercado por BEGIN TRANSACTION/ COMMIT), ele iniciará uma transação chamada CreatProc transaction. Procure-o e o [Transaction SID]SID de login que você deseja.

    Outra possibilidade é procurar a transação que adquiriu um SCH_M na view que você deseja:

    select [Lock Information], * 
from fn_dblog(null, null)
where [Lock Information] like '%' + cast(object_id('...') as varchar(10))+'%'
and [Lock Information] like '%LOCK_SCH_M%'
go

    Observe que se a exibição foi alterada por DROP seguido por CREATE, o ID do objeto provavelmente foi alterado, mas pelo menos você obterá a última transação que fez o CREATE (o ID do objeto atual da exibição no banco de dados restaurado). Com o ID da transação, você volta e recupera as informações da transação inicial:

    select [Begin Time], [Transaction Name], [Transaction SID], *
from fn_dblog(null, null)
where [Transaction ID] = '...'
and Operation = 'LOP_BEGIN_XACT';

    O [SID da transação] é, novamente, seu cara. Use SUSER_SNAMEpara recuperar o nome de login do SID de login. Se o SID for 0x01 significa que o login foi sa, o que significa que qualquer pessoa que saiba a sasenha poderia ter feito isso.

    • 19

  3. Não, a menos que você o tenha registrado por meio de um gatilho DDL ou algo semelhante

    Você deseja verificar quem possui direitos ALTER nesse banco de dados ou associação à função sysadmin/db_owner/ddl_admin. Isso seria melhor como uma revisão geral do que como uma caça às bruxas. Provavelmente existem outras pessoas com direitos para fazer alterações não aprovadas e não autorizadas também

    • 6

  4. Se você ainda não o fez, talvez queira verificar o relatório Histórico de alterações de esquema disponível no SQL Server Management Studio. Parece que o SQL Server registra as alterações por padrão ( rastreamento padrão ) e você deve poder visualizar esses dados por meio deste relatório. A única coisa lamentável é que esses arquivos de rastreamento são excluídos / substituídos automaticamente com o passar do tempo, portanto, os dados podem já ter desaparecido. Boa sorte!

    • 0

relate perguntas