User Audit

alien251 · 2009-10-24

I'm trying to log who deletes sales orders using a trigger on the Sales Header table, the problem I'm having is the return value of the CURRENT_USER function I'm calling in the trigger I created on.

So here's the trigger, I know this is not a T-SQL forum but the question I'm asking is in the context of how Navision authenticates a user to the database server...

Here's the table to contain the audit information.

CREATE TABLE SO_AUDIT (SO_NUM VARCHAR(20),DATE_DELETED DATETIME,USERNAME VARCHAR(120))

Here's the trigger.

CREATE TRIGGER dbo.Audit_SO_Deletions
ON dbo.[Company Name$Sales Header]
FOR DELETE
AS
BEGIN
INSERT SO_AUDIT
SELECT [No_], GETDATE(),CURRENT_USER
FROM DELETED
END
GO

Here's the result...
SO_NUM DATE_DELETED USERNAME

SO2009-01843 2009-10-23 16:42:52.187 dbo
SO2009-01844 2009-10-23 16:46:06.627 $ndo$ar$000005010500000000000015F2D9D174D68A37E84DF9B9860000051B
SO2009-01845 2009-10-23 16:50:23.663 $ndo$ar$000005010500000000000015F2D9D174D68A37E84DF9B9860000051B

My question is what the heck is this....?
$ndo$ar$000005010500000000000015F2D9D174D68A37E84DF9B9860000051B

The first entry was made by me, because I'm a member of the sysadmin role it shows as dbo...

Thanks!!!

Dan

jolly_man · 2009-10-24

alien251 wrote:

I'm trying to log who deletes sales orders using a trigger on the Sales Header table, the problem I'm having is the return value of the CURRENT_USER function I'm calling in the trigger I created on.

So here's the trigger, I know this is not a T-SQL forum but the question I'm asking is in the context of how Navision authenticates a user to the database server...

Here's the table to contain the audit information.

CREATE TABLE SO_AUDIT (SO_NUM VARCHAR(20),DATE_DELETED DATETIME,USERNAME VARCHAR(120))

Here's the trigger.

CREATE TRIGGER dbo.Audit_SO_Deletions
ON dbo.[Company Name$Sales Header]
FOR DELETE
AS
BEGIN
INSERT SO_AUDIT
SELECT [No_], GETDATE(),CURRENT_USER
FROM DELETED
END
GO

Here's the result...
SO_NUM DATE_DELETED USERNAME
SO2009-01843 2009-10-23 16:42:52.187 dbo
SO2009-01844 2009-10-23 16:46:06.627 $ndo$ar$000005010500000000000015F2D9D174D68A37E84DF9B9860000051B
SO2009-01845 2009-10-23 16:50:23.663 $ndo$ar$000005010500000000000015F2D9D174D68A37E84DF9B9860000051B

My question is what the heck is this....?
$ndo$ar$000005010500000000000015F2D9D174D68A37E84DF9B9860000051B

The first entry was made by me, because I'm a member of the sysadmin role it shows as dbo...

Thanks!!!

Dan

But this can be easily solved in NAVISION with about 5 lines of code, in the Sales Header table, ondelete() trigger.

matteo_montanari · 2009-10-24

jolly_man wrote:

alien251 wrote:

I'm trying to log who deletes sales orders using a trigger on the Sales Header table, the problem I'm having is the return value of the CURRENT_USER function I'm calling in the trigger I created on.

So here's the trigger, I know this is not a T-SQL forum but the question I'm asking is in the context of how Navision authenticates a user to the database server...

Here's the table to contain the audit information.

CREATE TABLE SO_AUDIT (SO_NUM VARCHAR(20),DATE_DELETED DATETIME,USERNAME VARCHAR(120))

Here's the trigger.

CREATE TRIGGER dbo.Audit_SO_Deletions
ON dbo.[Company Name$Sales Header]
FOR DELETE
AS
BEGIN
INSERT SO_AUDIT
SELECT [No_], GETDATE(),CURRENT_USER
FROM DELETED
END
GO

Here's the result...
SO_NUM DATE_DELETED USERNAME
SO2009-01843 2009-10-23 16:42:52.187 dbo
SO2009-01844 2009-10-23 16:46:06.627 $ndo$ar$000005010500000000000015F2D9D174D68A37E84DF9B9860000051B
SO2009-01845 2009-10-23 16:50:23.663 $ndo$ar$000005010500000000000015F2D9D174D68A37E84DF9B9860000051B

My question is what the heck is this....?
$ndo$ar$000005010500000000000015F2D9D174D68A37E84DF9B9860000051B

The first entry was made by me, because I'm a member of the sysadmin role it shows as dbo...

Thanks!!!

Dan

But this can be easily solved in NAVISION with about 5 lines of code, in the Sales Header table, ondelete() trigger.

Or using the standard log functionality

"$ndo$ar$000005010500000000000015F2D9D174D68A37E84DF9B9860000051B" is the standard login functionality of Navision. It's a internal random generated Login that prevent a normal user with a valid navision login to bypass nav and access with his credential directly to SQL Nav database...

bye

Matteo

Slawek_Guzek · 2009-10-24

Use ORIGINAL_LOGIN() function instead of CURRENT_USER();

Just remember that your trigger will be probably removed each time you design or import Sales Header table...Double check this at it may be nasty surprise...

Slawek

rdebath · 2009-10-25

Slawek Guzek wrote:

Just remember that your trigger will be probably removed each time you design or import Sales Header table...Double check this at it may be nasty surprise...
Slawek

That's not true; Navision never deletes a table, it always uses table alter statements. IME the only way to get rid of the trigger is to explicitly drop it or delete the table it's attached to.

Add columns, remove columns, renumber the table even rename the table (or the company) and the trigger is still there. Delete all the columns and the table won't save, add a new one and the trigger is STILL there!

:whistle: The cat came back, they thought he was a goner. But the cat came back, he just wouldn't stay away ... :twisted:

Slawek_Guzek · 2009-10-25

Good to know. I wasn't sure that's why I told 'check this'.

Slawek

reijermolenaar · 2009-10-25

You can also use SYSTEM_USER...

SET @Username = UPPER(SYSTEM_USER);
IF (CHARINDEX('\', @Username) > 0)
  SET @Username = SUBSTRING(@Username, CHARINDEX('\', @Username) + 1, 100)

alien251 · 2009-10-26

Perfect, thank you...!

Dan

User Audit

Answers

Categories