0

I have a trigger in "Contracts" and I also have a table called "Audits" (self explanatory).

Everything is working fine. If I insert, edit or delete, a row is inserted into Audits table by the trigger...

The problem here is that Trigger does not accept parameters... and I have a table column called "TriggeredBy" inside of the Audits table... which is supposed to have the User's ID (whoever did the insert, delete or UPDATE).

Is there a workaround that I can use so I can pass that value to that trigger?

Bekal
  • 19
  • 1
  • Is using current_user function an option? – Tarik Nov 15 '20 at 10:12
  • @Tarik It is not sadly (From my understanding, it returns the name of the current SQL user?). The users are not SQL users. The SQL user is the same for everyone – Bekal Nov 15 '20 at 10:15
  • Using a stored procedure might be a solution. I assume it is a web application. – Tarik Nov 15 '20 at 10:22
  • @Tarik It's a desktop application and I'm using a stored procedure to insert... but how would I link the stored procedure to the trigger exactly? since the trigger is supposed to run on its own. – Bekal Nov 15 '20 at 10:25
  • Do you keep an open db connection while the application is running until the user closes the application? Or do you open a connection, select, update or delete and close the connection? – Tarik Nov 15 '20 at 10:28
  • Hardcoding a single user ID and password in an application in a .Net application is extremely insecure. It takes five minutes to decompile the application and retrieve the credentials leaving your database wide open. – Tarik Nov 15 '20 at 10:36
  • There is no "work around" triggers don't work like that. You don't call triggers, they activate based on the type of DML statement you use. You parametrise statements you called, like `SELECT`/`INSERT`/`EXEC {Procedure}`. If you want to use a trigger like that you likely have a design flaw, or are they kg to implement ba solution with one, in my opinion. – Thom A Nov 15 '20 at 10:53

2 Answers2

0

If you have the db connection opened for the duration of the application, you can keep track of who is associated with the current db session by having a table with session if, user id.

SessionId int, UserId varchar(20)

At login time, use @@SPID to store the session ID and associated user.

The trigger can then use @@SPID and retrieve the user ID from the table and insert it into the log table.

Option 2: Use an application role. Allow users to connect to SQL server database using Windows Integrated Security. Call sp_setapprole to set the role. Users should be given no access to any table. The app role should have insert update delete.

You can now determine the user in your trigger.

Tarik
  • 10,810
  • 2
  • 26
  • 40
  • The DBConnection gets opened only when a user execute a query, then get closed again, so I'm assuming that's no the way to go? What do you think of the following as a solution? https://learn.microsoft.com/en-us/sql/t-sql/statements/set-context-info-transact-sql?view=sql-server-ver15 – Bekal Nov 15 '20 at 10:42
  • Well, the context info solution seems to be a solution. Yet, I think the application role is better. – Tarik Nov 15 '20 at 10:44
  • @Tarik, I suggest `SESSION_CONTEXT` instead of `CONTEXT_INFO` if you are using SQL 2016 or later. – Dan Guzman Nov 15 '20 at 11:18
0

If the desktop application used Windows authentication, you could simply use ORIGINAL_LOGIN() or SUSER_SNAME() to get the end user account name in trigger code.

With a shared SQL login, one method is to store the end user name in SQL session context for use by the trigger. Session context allows you to store name/value pairs using the sp_set_session_context procedure and read current session values with the SESSION_CONTEXT function. Call sp_set_session_context with the current user name after opening a new SQL connection so that it can be used by triggers to identify the end user.

Example T-SQL code below. Also, see this answer for other methods to set/use session level values.

CREATE TRIGGER TR_YourTable
ON dbo.YourTable
FOR INSERT, UPDATE, DELETE
AS
DECLARE @TriggeredBy sysname = COALESCE(CAST(SESSION_CONTEXT(N'end-user-name') AS sysname), N'unknown');
IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
BEGIN
    INSERT INTO dbo.YourAuditTable (Action, SomeColumn, TriggeredBy)
    SELECT 'updated', SomeColumn, @TriggeredBy
    FROM deleted;
END
ELSE
BEGIN
    IF EXISTS(SELECT 1 FROM inserted)
    BEGIN
        INSERT INTO dbo.YourAuditTable (Action, SomeColumn, TriggeredBy)
        SELECT 'inserted', SomeColumn, @TriggeredBy
        FROM inserted;
    END
    ELSE
    BEGIN
        INSERT INTO dbo.YourAuditTable (Action, SomeColumn, TriggeredBy)
        SELECT 'deleted', SomeColumn, @TriggeredBy
        FROM deleted;
    END;
END;
GO



--Example T-SQL usage. Queries should be parameterized in application code.
EXEC sp_set_session_context N'end-user-name', N'me';
INSERT INTO dbo.YourTable (SomeColumn) VALUES('example');
GO
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Thanks I'll give this a try. What do you think of creating temporary tables though. AFAIK it can be called from a trigger? – Bekal Nov 15 '20 at 11:36
  • You can use a local temporary table, although the implication is the trigger will fail if it doesn't exist. – Dan Guzman Nov 15 '20 at 11:40
  • If going for the context solution, I would not use Windows Integrated authentication as it would leave to the database wide open. As for using the context solution, it requires each connection to the database to be followed by a call to sp_set_session_context. From a security point of view this can also be circumvented by decompiling the code, getting the shared user and password then executing sp_set_session_context to impersonate anybody. The application role is not perfect but can make anyone doing direct updates to the database be held accountable if a trigger is in place to log updates. – Tarik Nov 15 '20 at 11:48