none
Trigger de Logon com Texto da Query RRS feed

  • Pergunta

  • Senhores,
      Tenho a seguinte query que utilizo em uma trigger de logon:

      SELECT
    ORIGINAL_LOGIN() AS [nm_login],
    CONVERT(SMALLDATETIME, GETDATE(), 103) AS [dh_login],
    @@SPID AS [id_sessao],
    HOST_NAME() AS [host_name],
    APP_NAME() AS nm_programa,
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('client_net_address')) AS [ds_ip],
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('net_transport')) AS [ds_protocolo],
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('protocol_type')) AS [tp_protoloco]

      Preciso inserir a query executada pelo usuário.
      Alguma dica ?

    Obrigado,
    Paulo

    terça-feira, 27 de outubro de 2015 15:14

Respostas

  • Acredito que esta questão seja mais complicada, uma vez que o SQL armazena o último batch para cada sessão e isto é vinculado ao sql_handle.

    Quando você executa um batch com a query para retornar o último batch sempre obterá a query(batch) que busca o último batch, para resumir, é como um cão que corre atrás do próprio rabo.

    Com as queries fornecidas aqui você até consegue retornar o que deseja, mas apenas executando em outra sessão(conexão).

    Outra opção tomando o SPID 52 como exemplo:

    DBCC INPUTBUFFER(52)



    quarta-feira, 18 de novembro de 2015 15:55
  • Amigo,

    Se a sua instrução esta retornando corretamente a sessão do usuário, então você pode fazer uma consulta "cruzando" a tabela de sistema "sysprocesses" com a DMV "dm_exec_sql_text".

    Segue abaixo um script T-SQL para você adaptar à sua necessidade:

    SELECT
    ORIGINAL_LOGIN() AS [nm_login],
    CONVERT(SMALLDATETIME, GETDATE(), 103) AS [dh_login],
    @@SPID AS [id_sessao],
    HOST_NAME() AS [host_name],
    APP_NAME() AS nm_programa,
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('client_net_address')) AS [ds_ip],
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('net_transport')) AS [ds_protocolo],
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('protocol_type')) AS [tp_protoloco]
    , st.[text] AS [ds_instrucao]
    FROM master..sysprocesses p
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
    where p.spid = @@SPID
    GO

    Segue abaixo um print-screen como evidência de teste:

    Para maiores informações veja:

    https://technet.microsoft.com/pt-br/library/ms181929%28v=sql.110%29.aspx

    https://msdn.microsoft.com/pt-br/library/ms179881(v=sql.120).aspx


    Se ajudou na sua solução, não esqueça de marcar como resposta !


    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    terça-feira, 27 de outubro de 2015 18:00

Todas as Respostas

  • Amigo,

    Se a sua instrução esta retornando corretamente a sessão do usuário, então você pode fazer uma consulta "cruzando" a tabela de sistema "sysprocesses" com a DMV "dm_exec_sql_text".

    Segue abaixo um script T-SQL para você adaptar à sua necessidade:

    SELECT
    ORIGINAL_LOGIN() AS [nm_login],
    CONVERT(SMALLDATETIME, GETDATE(), 103) AS [dh_login],
    @@SPID AS [id_sessao],
    HOST_NAME() AS [host_name],
    APP_NAME() AS nm_programa,
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('client_net_address')) AS [ds_ip],
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('net_transport')) AS [ds_protocolo],
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('protocol_type')) AS [tp_protoloco]
    , st.[text] AS [ds_instrucao]
    FROM master..sysprocesses p
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
    where p.spid = @@SPID
    GO

    Segue abaixo um print-screen como evidência de teste:

    Para maiores informações veja:

    https://technet.microsoft.com/pt-br/library/ms181929%28v=sql.110%29.aspx

    https://msdn.microsoft.com/pt-br/library/ms179881(v=sql.120).aspx


    Se ajudou na sua solução, não esqueça de marcar como resposta !


    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    terça-feira, 27 de outubro de 2015 18:00
  •   Obrigado pelo retorno, Durval.

      Eu já tinha tentado isso e funciona, porém apenas quando eu estou executando em meu próprio Management Studio.
      Quando deixo a trigger executar, ele me retorna o texto da própria trigger.

    quinta-feira, 29 de outubro de 2015 12:53
  • SupremeFoxPC,

    Então com qual conta de usuário este Trigger esta sendo executado? Talvez isso pode esta impactando justamente na obtenção dos dados.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 30 de outubro de 2015 16:05
    Moderador
  • Acredito que esteja sendo executado como "sa", mesmo.
    Texto da trigger hoje:

    ALTER TRIGGER [AuditoriaUsuario] ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS
    BEGIN
      IF (ORIGINAL_LOGIN() NOT IN ('AUTORIDADE NT\SISTEMA')) AND (APP_NAME() NOT IN ('AutoMate 9'))
      BEGIN
        BEGIN TRY
          INSERT INTO MASTER.DBO.AuditoriaUsuario
     SELECT
    ORIGINAL_LOGIN() AS [nm_login],
    CONVERT(SMALLDATETIME, GETDATE(), 103) AS [dh_login],
    @@SPID AS [id_sessao],
    HOST_NAME() AS [host_name],
    APP_NAME() AS nm_programa,
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('client_net_address')) AS [ds_ip],
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('net_transport')) AS [ds_protocolo],
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('protocol_type')) AS [tp_protoloco]
    END TRY BEGIN CATCH END CATCH
      END
    END


    quinta-feira, 5 de novembro de 2015 11:04
  • SupremeFoxPC,

    Certo, realmente o contexto de execução esta sendo trocado para o usuário SA!!!

    Você teria que verificar os tipos de dados que estão definidos na sua tabela auditoria, talvez possa estar ocorrendo alguma incompatibilidade com o dado que esta sendo obtido em relação ao tipo de dados que estão sendo utilizado para gravar.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 5 de novembro de 2015 16:08
    Moderador
  • Acredito que esta questão seja mais complicada, uma vez que o SQL armazena o último batch para cada sessão e isto é vinculado ao sql_handle.

    Quando você executa um batch com a query para retornar o último batch sempre obterá a query(batch) que busca o último batch, para resumir, é como um cão que corre atrás do próprio rabo.

    Com as queries fornecidas aqui você até consegue retornar o que deseja, mas apenas executando em outra sessão(conexão).

    Outra opção tomando o SPID 52 como exemplo:

    DBCC INPUTBUFFER(52)



    quarta-feira, 18 de novembro de 2015 15:55