none
@@SPID dentro de trigger retornando conexão "errada" RRS feed

  • Pergunta

  • Bom dia, utilizo triggers para realizar log de alterações em algumas tabelas de nosso sistema, porém algumas vezes não consigo obter corretamente o usuário que disparou aquele procedimento. Exemplo

    Quando o usuário entra no sistema, eu gravo, em uma tabela o Id da Conexao, através do @@SPID. Então Minha tabela fica assim

    Conexão - Código do Usuário - Nome do Usuário

    20 - 100 - Jose

    21 - 50 - Carlos

    Dentro da trigger uma das primeiras coisas que faço é dar um select nesta tabela da seguinte forma

    Select Codigo_Usuario, Nome From Tabela where Conexao = @@SPID

    Desta forma, insiro corretamente na tabela de log, o usuário que fez aquela alteração, porém várias vezes acontece de @@SPID vir uma conexão totalmente diferente das que possuo nesta tabela de controle, como se fosse conexão criada apenas pelo SQL server para realizar alguma operação.

    No sistema a ordem das operações acontecem assim

    1- Sistema efetua a chamada de uma procedure. A conexão passada foi por exemplo a 20.

    2 - Dentro da procedure é executado o update na tabela que possui a trigger. Neste caso meu comando "Select Codigo_Usuario, Nome From Tabela where Conexao = @@SPID" deveria retornar o usuario 100, de nome Jose. Mas varias vezes não retorna nada, e estou vendo que o conteúdo da @@SPID é um que não tem em minha tabela de controle.

    Pergunta, O @@SPID não fica "pendurado em todas as operações", de forma que deveria me retornar corretamente?

    segunda-feira, 18 de setembro de 2017 13:16

Respostas

  • Danilo,

    Provavelmente por o processo de execução do trigger esta sendo realizado de forma rápido o que faz o SQL Server trazer o dados do SPID anterior que disparou o trigger.


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

    quinta-feira, 28 de setembro de 2017 00:13
  • Deleted
    quinta-feira, 21 de setembro de 2017 05:06

Todas as Respostas

  • Acredito que @@SPID caia no mesmo conceito da problemática  do IDENTITY dentro de uma trigger.

    trigger e muito lindo ,entretanto existem muitos cuidados de programação, logo nosso amigo José diz tem um excelente artigo 

    Armadilhas na programação de procedimentos de gatilho.

    agora, eu acredito que a não ser por regra de negocio, não faz sentido vc auditar a sessão aberta de um  query, não tem valor algum saber em auditoria. as o usuario logado que iniciou a sessão sim ,faz todo sentido , 

    eu trocaria seu @@SPID

    por ORIGINAL_LOGIN()

    leitura :https://stackoverflow.com/questions/6169932/original-login-and-system-user-functions-for-auditing

    Wesley Neves - Brasilia-DF

     
    https://wesleyneves.wordpress.com/
    MTA-SQL Server
    MTA- Web Development
    Analista Desenvolvedor.NET
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"





    Wesley Neves


    segunda-feira, 18 de setembro de 2017 13:44
  • Obrigado Wesley, porém eu preciso chegar a esta tabela (de controle minha quando o usuário loga no sistema) para saber exatamente qual usuário fez aquela alteração na tabela, e gerar log.

    A única maneira que achei até agora, é através da @@SPID.

    Outro exemplo que me faz ser importante é a seguinte:

    A authenticação no sql server através de windows authentication (com um usuário genérico, exemplo "Recepcao_Escola", e o controle de acesso a telas do sistema todo feito via aplicação). Desta forma, vejo apenas o ID da conexão para fazer consulta a esta tabela de controle e identificar o usuário.

    segunda-feira, 18 de setembro de 2017 18:55
  • Bom dia Danilo Rogério , posta o código da sua trigger por favor. Wesley Neves - Brasilia-DF

     
    https://wesleyneves.wordpress.com/
    MTA-SQL Server
    MTA- Web Development
    Analista Desenvolvedor.NET
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"





    Wesley Neves

    terça-feira, 19 de setembro de 2017 11:27
  • Wesley vou suprimir parte do código da trigger que busca os dados das tabelas INSERTED e DELETED, colocando apenas a parte que me interessa na obtenção dos dados do usuário

    CREATE Trigger [dbo].[TR_Log_Tabela_FIUD]
    On [dbo].[Tabela]
    For INSERT, UPDATE, DELETE
    AS BEGIN
    
    	SET NOCOUNT ON;
    
    	Declare @Sistema Int
    	Declare @Usuario Int
    	Declare @Ip Varchar(30)
    	Declare @Maquina Varchar(30)
    	Declare @Programa Varchar(200)
    
    	--Aqui ficam as rotinas de captura das variáveis da tabela Inserted e Deleted
    
    	--Monta o Select que armazena as variáveis de sistema: Usuario, Sistema, IP
    	If Exists(Select * From UsuarioNoServidor
    		 Where SPID = @@SPID
    		  And Upper(NomeServidor) = Upper(cast(SERVERPROPERTY('ServerName') as varchar(30)))) Begin
    		Select
    			@Sistema = Sistema,
    			@Usuario = CodigoUsuario,
    			@Ip = Ip,
    			@Maquina = Maquina,
    			@Programa = Programa
    		From UsuarioNoServidor
    		Where SPID = @@SPID
    			 And Upper(NomeServidor) = Upper(cast(SERVERPROPERTY('ServerName') as varchar(30)))
    
    		--Caso a Tabela não esteja vazia e já exista o SPID
    		--Vejo se é o proprio servidor que "pegou" aquele SPID. Exemplos: Processos de Job
    		If Upper(Host_Name()) = Upper(cast(SERVERPROPERTY('ServerName') as varchar(30))) Begin
    			Set @Sistema = 0
    			Set @Usuario = 0
    			Set @Ip = IsNull((SELECT distinct a.client_net_address
    								 FROM sys.dm_exec_connections a
    								 Where a.session_id = @@SPID),'1')
    			Set @Maquina = Host_Name()
    			Set @Programa = ''        
    		End Else Begin
    			--Vejo se é a maquina é diferenta da que está na tabela
    			If Upper(Host_Name()) <> Upper(@Maquina) Begin
    				Set @Sistema = 0
    				Set @Usuario = 0
    				Set @Ip = IsNull((SELECT distinct a.client_net_address
    						 FROM sys.dm_exec_connections a
    						 Where a.session_id = @@SPID),'2')
    				Set @Maquina = Host_Name()
    				Set @Programa = ''
    			End
    		End
    	End Else Begin
    		Set @Sistema = 0
    		Set @Usuario = 0
    		Set @Ip = IsNull((SELECT distinct a.client_net_address
    				 FROM sys.dm_exec_connections a
    				 Where a.session_id = @@SPID),'3')
    		Set @Maquina = Host_Name()
    		Set @Programa = ''
    	End
    
    	--Daqui para baixo apenas as verificações de inclusão/alteração/deleção e inserção no Log
    
    
    End



    • Editado Danilo Rogério terça-feira, 19 de setembro de 2017 14:17 comando sql
    terça-feira, 19 de setembro de 2017 12:55
  • Danilo,

    Esse é um comportamento normal, pois quando utilizamos trigger o SQL Server cria em tempo de execução um sessão para o processamento desta transação dedicada ao trigger e por isso o @@SPID vai retornar outro número.


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

    quinta-feira, 21 de setembro de 2017 00:47
  • Deleted
    quinta-feira, 21 de setembro de 2017 05:06
  • Júnior Galvão, bom dia.

    O que é estranho é que mesmo havendo essa nova sessão para a trigger, há momentos, e a maioria deles, que o SPID é retornado corretamente.

    quinta-feira, 21 de setembro de 2017 10:58
  • Boa tarde, Danilo Rogério . Tudo bem?

    Você conseguiu o que precisava? Se ainda não, por favor nos diga o que ainda falta.

    Atenciosamente,

    Filipe B de Castro

    Esse conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita

    MSDN Community Support

    Por favor, lembre-se de Marcar como Resposta as postagens que resolveram o seu problema. Essa é uma maneira comum de reconhecer aqueles que o ajudaram e fazer com que seja mais fácil para os outros visitantes encontrarem a resolução mais tarde.

    sexta-feira, 22 de setembro de 2017 20:24
    Moderador
  • Filipe, bom dia.

    Os links do pessoal ajudaram, mas não parece ter muita solução. Enfim, continuo com o problema

    segunda-feira, 25 de setembro de 2017 13:00
  • Danilo,

    Provavelmente por o processo de execução do trigger esta sendo realizado de forma rápido o que faz o SQL Server trazer o dados do SPID anterior que disparou o trigger.


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

    quinta-feira, 28 de setembro de 2017 00:13