NAV User on SQL trigger

Comments

• Options

  Marije_Brummel Member, Moderators Design Patterns Posts: 4,262

  2015-07-06

  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.

  Marije Brummel | Freelance Business Central Specialist

  0
• Options

  aavio Member Posts: 143

  2015-07-07

  can you try with SYSTEM_USER at the SQL trigger.

  aav!o

  0
• Options

  fzarateo Member Posts: 10

  2015-07-07

  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.

  0
• Options

  rmv_RU Member Posts: 119

  2015-07-09

  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 .

  Looking for part-time work.
  Nav, T-SQL.

  0
• Options

  fzarateo Member Posts: 10

  2015-07-15

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

  0