I would like to achieve the following:
Users log into a web app, using their credentials from the users
table.
After inserting a row into the inserttest
table, a trigger activates in which it creates a new row in the log_audit
table, with one of the values as the userid
of the user who is currently logged in.
Is it possible to script this entirely in T-SQL? I can't seem to get my trigger to work properly.
Webapp connects via service account. It only uses one login in SQL Server (the one that is used in the connection string in the webconfig).
This is what I have:
CREATE TABLE inserttest (
id INT NOT NULL PRIMARY KEY IDENTITY,
name VARCHAR(20) NULL,
)
CREATE TABLE users (
id INT NOT NULL PRIMARY KEY IDENTITY,
name VARCHAR(20) NULL
)
CREATE TABLE log_audit (
logid INT PRIMARY KEY IDENTITY,
userid INT NOT NULL,
logdate DATE NULL
)
CREATE TRIGGER tgr_insert ON inserttest
AFTER UPDATE, INSERT
AS BEGIN
INSERT INTO log_audit (userid, logdate)
VALUES (@USERIDofCURRENTAPPUSERinUSERSTABLE, GETDATE)
END -- trigger script will not work of course
Best Answer
If the application connects to SQL Server with service account credentials instead of current user credentials, the code will need to set
CONTEXT_INFO
,SESSION_CONTEXT
, or save the user name to a table keyed bysession_id
as described in this answer. The value can then be used in the trigger code.SESSION_CONTEXT
is the most elegant but is only available in SQL Server 2016 and SQL Azure Database.