none
Audit de indexes RRS feed

  • Pergunta

  • Olá,

     

    Eu gostaria de implementar uma auditoria apenas para indexes, contendo as informações (algo como nome do index, quem criou, base e tabela em que foi criado ou dropado) em uma tabela, é possivel? dei uma procurada na internet mas nao consegui encontrar nada especifico....

    terça-feira, 18 de janeiro de 2011 18:19
    Moderador

Respostas

  • Você vai pegar essas informações atraves de uma séries da dados (xml) vindas do EventData.

    A estrutura do EventData ( http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd ) varia de acordo com o evento, procure por Drop_Trigger no xsd e vai ver a estrutura.

    De qualquer forma, como exemplo, eu estou enviando um exemplo de como trabalhar com ele:

     

    CREATE TABLE Log_DBObjetos
    (
    	ID_EVENTO      integer IDENTITY (1,1) ,
    	HOST_NAME      varchar(100) NULL ,
    	LOGIN_NAME     varchar(100) NULL ,
    	CLIENT_NET_ADDRESS varchar(15) NULL ,
    	EVENT_DATE     datetime   NULL ,
    	EVENT_TYPE     varchar(50) NULL ,
    	OBJECT_NAME     varchar(100) NULL ,
    	DB_NAME       varchar(20) NULL,
    	OBJECT_TYPE     varchar(20) NULL
    
    )
    go
    
    
    
    
    Dentro da Trigger:
    /********************************************************************************************************/
    
    declare @data xml
    set @data = EVENTDATA()
    
    SET NOCOUNT ON
    
    Insert Into [Log_DBObjetos]
    		  (HOST_NAME, LOGIN_NAME, CLIENT_NET_ADDRESS, EVENT_DATE, EVENT_TYPE, OBJECT_NAME, DB_NAME, OBJECT_TYPE)
    		Select Distinct
    			  Host_Name, 
    			  Login_Name,
    			  Client_Net_Address,
    			  getdate() as DataAtual,
    			  @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') as EventType, 
    			  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)') as ObjectName,
            db_name() as DBName,
    			  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(256)') as ObjectType
    		 from sys.dm_exec_sessions
    			  Inner Join sys.dm_exec_connections On sys.dm_exec_sessions.Session_Id = sys.dm_exec_connections.Session_Id
    		 Where sys.dm_exec_connections.Session_Id = @@SPID
    		Order by Login_Name, Host_Name
    
    /********************************************************************************************************/
    
    


    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco
    terça-feira, 18 de janeiro de 2011 19:08

Todas as Respostas

  • Caputofa, para isso você vai precisar criar uma DDL Trigger ( http://msdn.microsoft.com/en-us/library/ms186406.aspx ) utilizando como escopo o banco e nos eventos  (http://msdn.microsoft.com/en-us/library/bb522542.aspx ) CREATE_INDEX, ALTER_INDEX e DROP_INDEX.



    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco
    terça-feira, 18 de janeiro de 2011 18:31
  • Fausto, é isso msm!!!...

     

    Uma ultima duvida, a trigger eu terei que criar por base né? ou seja, se possuo 60 bases, serão 60 trigger e 60 tabelas correto?

    terça-feira, 18 de janeiro de 2011 18:40
    Moderador
  • Exatamente. As tabelas é que você pode centralizar em um unico database.

    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco
    terça-feira, 18 de janeiro de 2011 18:42
  • Entendi, estou tentando criar aqui uma que na vdd barra o drop de um indice:

     

    create trigger rollback_drop_index

    on testes

    for drop_index

    as

    PRINT('IMPOSSIVEL DE SE DROPAR UM INDICE')

    rollback

     

    Mas esta dando erro, o que esta errado?

    terça-feira, 18 de janeiro de 2011 18:50
    Moderador
  • Caputofa, o escopo é "ON DATABASE" mesmo não "ON [Nome do seu Banco]"

     

    create trigger rollback_drop_index
      ON DATABASE
    for drop_index
    as
      PRINT('IMPOSSIVEL DE SE DROPAR UM INDICE')
      rollback
    
    


    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco
    terça-feira, 18 de janeiro de 2011 18:54
  • Agora funcionou Fausto, muito obrigado!

     

    Uma ultima duvida, se eu quiser pegar a tabela do indice, a data, o indice e o usuario que efetuou o comando e gravar na tabela X, como ficaria? 

    Estou com dificuldades em pegar todas as informacoes menos a data.....=\

     

     

    terça-feira, 18 de janeiro de 2011 18:56
    Moderador
  • Você vai pegar essas informações atraves de uma séries da dados (xml) vindas do EventData.

    A estrutura do EventData ( http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd ) varia de acordo com o evento, procure por Drop_Trigger no xsd e vai ver a estrutura.

    De qualquer forma, como exemplo, eu estou enviando um exemplo de como trabalhar com ele:

     

    CREATE TABLE Log_DBObjetos
    (
    	ID_EVENTO      integer IDENTITY (1,1) ,
    	HOST_NAME      varchar(100) NULL ,
    	LOGIN_NAME     varchar(100) NULL ,
    	CLIENT_NET_ADDRESS varchar(15) NULL ,
    	EVENT_DATE     datetime   NULL ,
    	EVENT_TYPE     varchar(50) NULL ,
    	OBJECT_NAME     varchar(100) NULL ,
    	DB_NAME       varchar(20) NULL,
    	OBJECT_TYPE     varchar(20) NULL
    
    )
    go
    
    
    
    
    Dentro da Trigger:
    /********************************************************************************************************/
    
    declare @data xml
    set @data = EVENTDATA()
    
    SET NOCOUNT ON
    
    Insert Into [Log_DBObjetos]
    		  (HOST_NAME, LOGIN_NAME, CLIENT_NET_ADDRESS, EVENT_DATE, EVENT_TYPE, OBJECT_NAME, DB_NAME, OBJECT_TYPE)
    		Select Distinct
    			  Host_Name, 
    			  Login_Name,
    			  Client_Net_Address,
    			  getdate() as DataAtual,
    			  @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') as EventType, 
    			  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)') as ObjectName,
            db_name() as DBName,
    			  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(256)') as ObjectType
    		 from sys.dm_exec_sessions
    			  Inner Join sys.dm_exec_connections On sys.dm_exec_sessions.Session_Id = sys.dm_exec_connections.Session_Id
    		 Where sys.dm_exec_connections.Session_Id = @@SPID
    		Order by Login_Name, Host_Name
    
    /********************************************************************************************************/
    
    


    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco
    terça-feira, 18 de janeiro de 2011 19:08
  • Fausto, uma ultima informação, como eu pego a tabela em do indice?....

     

    OBS: não consegui abrir o link em relacao ao xml para pegar a informação da tabela...

    terça-feira, 18 de janeiro de 2011 19:22
    Moderador
  •  

    Use o /EVENT_INSTANCE/TargetObjectName

     


    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco
    terça-feira, 18 de janeiro de 2011 19:34
  • Aew, muito obrigado Fausto!
    terça-feira, 18 de janeiro de 2011 19:50
    Moderador
  • Acrescentando as informações do Fausto,

    Tenho um post no meu blog que faz o que o fausto te sugeriu. Ele loga tabelas, procedures , indices e etc...

    Talvez te ajude também.

    http://fabriciodba.wordpress.com/2010/04/11/como-criar-um-controle-de-versao-de-procedures-views-e-functions-no-sql-server/

     


    Fabrício França Lima | MCP, MCTS, MCITP | Visite meu site: http://fabriciodba.wordpress.com/ | Dicas de artigos SQL: Siga-me no twitter @fabriciodba.
    terça-feira, 18 de janeiro de 2011 21:13
  • Fabricio,

     

    Foi sim de grande ajuda, muito obrigado, algumas bases minhas liguei o audit automatico (alias, fiquei impressionado, faz mto mais do que eu esperava), porem nas 2005, ja estou implementando as solucoes que obtive aqui =D

    quarta-feira, 19 de janeiro de 2011 01:36
    Moderador