@@SPID dentro de trigger retornando conexão "errada"

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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]

Answer 6

Deleted

Answer 7

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.

Answer 8

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.

Answer 9

Filipe, bom dia.

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

Answer 10

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]