May 12, 2016 at 2:10 pm
Like many, I inherited this and cannot change it, but I have run into an issue involving a DML trigger that I don't fully understand. So here is a summary:
- A stored procedure moves the most recent rows from an active logging table to an archive logging table and deletes rows from the archive logging table after a specified amount of time.
- There is annoying FOR INSERT, UPDATE, DELETE trigger on the archive table that will write rows to the active log table for each and every row that is inserted into, updated in, or deleted from the archive table. Like I said very annoying, I think it was a boneheaded idea created for auditing purposes by someone who did not know or did not understand SQL Server's auditing capabilities.
- There is a condition at the start of the DML trigger that calls a scalar valued function to check if the CALLER belongs to at least one or two database rolls. The function uses the IS_MEMBER() built-in function to verify this and thus requires an explicit login for the CALLER and corresponding database user in order for the IS_MEMBER() function to work. If the CALLER does belong to at least one of the two roles, then the function returns 1, the condition is consider true, the trigger should execute the RETURN statement exiting from the trigger, and further execution of trigger should not happen. The trigger code is like the following pseudo-code:
CREATE TRIGGER <<trigger_name>>
FOR INSERT, UPDATE, DELETE
AS
BEGIN
IF (<<user_created_scalar_function>>) = 1
RETURN
SET NOCOUNT ON;
DECLARE ...
...
This is where the issue arises. This process is executed by a scheduled SQL Server Agent job. The SQL Server Agent service account does have an explicit logon and it belongs to one of the two database roles that should satisfy the conditional statement within the trigger. However:
- If executed as it exists as a scheduled job calling the stored procedure, the trigger fires but the conditional statement appears to not be satisfied and the unnecessary logging described above occurs for each and every row that is inserted into and deleted from the archive logging table
- If I execute the stored procedure while impersonating the SQL Server Agent service account either as LOGIN or as USER in SSMS, the trigger does fire but the conditional statement is satisfied and there is NO unnecessary logging.
EXECUTE AS USER = <<service_account>>
EXECUTE <<stored_procedure>> <<parameter>>
REVERT
I am not sure why this is happening. I used Profiler to capture both scenarios and the trigger fires as I would expect it with one exception:
- When I impersonate the service account, the SP:StmtCompleted event for the conditional statement is followed by the SP:StmtStarting/SP:StmtCompleted events for the RETURN statement
- When the job runs, the SP:StmtCompleted event for the conditional statement is followed by the SP:StmtStarting/SP:StmtCompleted events for rest of the trigger code starting with SET NOCOUNT ON; as if the conditional statement was not satisfied.
However, when I walk through the events captured by Profiler, everything indicates that the conditional statement should be satisfied. Obviously this setup is VERY clunky but I can't change the process at this time. Any ideas why it works as designed through impersonation but not when run as a job?
May 12, 2016 at 2:47 pm
I think I figured it out with the help of this reference:
Looks like the original and effective logons from the job are the service account, but the effective database user is dbo. In comparison, when I impersonate the service account, the original logon is my account and the effective logon and database user are the service account.
For now, I overcame this by setting up the job step to execute as the service account in the advanced options of the job step properties.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply