I'm using the sys.sysprocesses table in SQL 2012/2014 , to identify through the CONTEXT_INFO field, which user of a certain application is bound to a particular session.
I created a function by passing% as_with%.
CREATE FUNCTION [dbo].[FC_RETORNAUSUARIO] (@spid INTEGER)
RETURNS sysname
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @username sysname;
SET @username = suser_sname() DECLARE @contextinfo varbinary(128)
SELECT @contextinfo = context_info
FROM master.dbo.sysprocesses
WHERE spid = @spid DECLARE @offset int DECLARE @usernamelength int
SET @usernamelength = substring(@contextinfo, datalength(@contextinfo), 1)
SET @offset = datalength(@contextinfo) - @usernamelength - sign(@usernamelength) IF @usernamelength <> 0x00
SET @username = convert(sysname, substring(@contextinfo, @offset, @usernamelength))
SET @offset = @offset - 1 RETURN @username;
END;
From this, I did a test with the application installed locally and next to the instance used to connect and worked. I got the user to pass the application.
But when I put it on a server, I noticed that the SPID information was empty. And that the user of the bank where the application was connected did not have integration with Windows, that is, it was a user created only for access to the bank.
Does not the user have integration with Windows (other than the server domain) that causes CONTEXT_INFO not to be filled?