Hello. In the database I have a trigger that saves the user who made the last change in the database. In the old system (still in use) the login with the database was made by AD (active directory) which allowed me to use the function SUSER_SNAME () to know who the user is. It turns out that in the new system the programmer changed the logic. Created a user table and logs the system with the database always with the SA user.
ALTER TRIGGER [dbo].[upd_ts_tord]
ON [dbo].[tord]
FOR UPDATE AS
IF (SELECT trigger_nestlevel()) = 1
BEGIN
IF (SELECT COUNT(*) FROM deleted WHERE audsta <> 0) = 0
BEGIN
UPDATE tord
SET updts = getdate(), updusr = suser_sname()
FROM tord, deleted
WHERE tord.ordid = deleted.ordid
END
ELSE
BEGIN
RAISERROR ('Updating of audit records is not permitted! Transaction cancelled!',16,1)
ROLLBACK TRANSACTION
END
END
Given the preliminary information, here's the question:
How can I make my trigger know who is logged in to the windows / domain regardless of which user logged into the database?
Is there something like: SUSER_ACTIVEDIRECTORY ()?