NAV User on SQL trigger

fzarateo

Hi all,
we have this situation on migration database.
With NAV 2009 we have SQL triggers to manage changes that could be replicated on other databases. And we have USERID from navision because our customer uses Classic client.
On nav 2015 with RTC we have the problem that the same triggers works fine but the user is always the user that start services.
How can we know who is the user that run this triggers from SQL?
Thanks in advance for your help

Marije_Brummel

You cannot.

I heard that someone made somthing I think in DOTNET to catch the user before the transaction starts. Still have to look at that solution.

aavio

can you try with SYSTEM_USER at the SQL trigger.

fzarateo

Thanks for your response, but we try with this

SELECT ORIGINAL_LOGIN() as A,
SUSER_NAME(SUSER_ID()) as B,
SUSER_SNAME(SUSER_SID()) as C,
SYSTEM_USER as D,
CURRENT_USER as E,
USER_NAME(USER_ID()) as F,
SESSION_USER as G,
USER as H
INTO TablaT

And all results are the same, the user that start service.
We try with ssesion Id and table active session in nav, but the Id session in nav is not the same that SQL.

rmv_RU

You can use a following scenario:
1. Create a sql view to let Nav know the current sql process id

create view
[dbo].[sys_ConnectionProperties]
as 
select cast(serverproperty('servername') as varchar(100)) as server_name, DB_NAME() as db_name, @@SPID as spid
	, system_USER as [system_user]
GO

2. Create the linked sys_ConnectionProperties table in Nav.
3. Write a function to link the current Nav user to a server process id using an additional table.

sys_ConnectionProperties.find('=');
serverProcesses.spid=sys_ConnectionProperties.spid
serverProcesses.navUser=USER();
if not serverProcesses.modify then serverProcesses.insert;

4. insert a function call in most suitable place - OnLoginStart or OnGlobalModify .
5. Modify SQL triggers.
6. Optimize the function - it's enough to modify serverProcesses table once per session .

fzarateo

Thanks for your help,
it works with little adjustments but the idea is great =D>