authentication - Executing SQL Script causes error in procedure called by trigger

Question

I'm also new to DevOps, but let me see if I can explain my situation, also if anyone has any advice on doing post deployment scripts and can share their experiences I will be most grateful.

I use SQL Source control from Redgate, Git and Azure DevOps. I have managed to get the build and deployment working perfectly. Additionally we would like to make some data changes so we have additional (Data Change) scripts we wish to run. Linking these tables as static data is not an option as we would end up having most tables linked and end up with extremely timeous build and deploy times.

The script inserts data to a table, thereby triggering the insert trigger which calls a procedure to write to an audit table. The actual error from the deployment log is:

Task         : DbUp Migration
2021-01-25T12:52:17.2629353Z Description  : Runs SQL Server change scripts, and only those which have not been run already.
2021-01-25T12:52:17.2629405Z Version      : 2.1.4
2021-01-25T12:52:17.2629671Z Author       : Johan Classon
2021-01-25T12:52:17.2629721Z Help         : [More Information](https://github.com/johanclasson/vso-agent-tasks)
2021-01-25T12:52:17.2629775Z ==============================================================================
2021-01-25T12:52:19.6610608Z Beginning database upgrade
2021-01-25T12:52:19.6689401Z Checking whether journal table exists..
2021-01-25T12:52:19.6728756Z Journal table does not exist
2021-01-25T12:52:19.7775348Z Executing Database Server script '001 - EX27605 - rdl.sql'
2021-01-25T12:52:19.7908913Z Checking whether journal table exists..
2021-01-25T12:52:19.7929056Z Creating the [dbo].[_SchemaVersions] table
2021-01-25T12:52:19.8077238Z The [dbo].[_SchemaVersions] table has been created
2021-01-25T12:52:20.2264323Z ifExists - rdl_Rule_Definition_Lookup -  rdl_Code = "9900226e"
2021-01-25T12:52:20.2277308Z insert - rdl_Rule_Definition_Lookup -  rdl_Code = "9900226e"
2021-01-25T12:52:20.2805780Z SQL exception has occured in script: '001 - EX27605 - rdl.sql'
2021-01-25T12:52:20.2942912Z ##[error]Script block number: 0; Block line 74; Message: Trig_After_Ins_Upd_Del_rdl_Rule_Definition_Lookup
2021-01-25T12:52:20.4528752Z ##[error]System.Data.SqlClient.SqlException (0x80131904): p_dte_Audit_Backend:  (Line: 108) [dte_admin]

So the 1st error refers to line 74 of the trigger, if I read this correctly which is a commit on a procedure call to write to the audit table, line 108 of my procedure sets the userID:

            DECLARE @user VARCHAR(40)
            SET @user = 
            CASE
                WHEN suser_sname() = 'NT SERVICESQLAgent$'+@@servicename THEN 'dte_admin'
                WHEN suser_sname() = 'NT SERVICESQLSERVERAGENT' THEN 'dte_admin'
                ELSE suser_sname()
            END 

--###############

          select @udt_Audit = udt_Audit 
    from udt_User_Detail (nolock)
          where udt_User_Id = @user--suser_sname()

          --select @udt_Audit = udt_Audit from udt_User_Detail
          --where udt_User_Id = suser_sname()
          
          if @@rowcount = 0
          begin
                declare @udt_User_Id varchar(30)
                set @udt_User_Id = suser_sname()
          

The last line being line 108.

Important to note that dte_admin is a login on the SQL Server, it is a sysadmin account and mapped to the database, the dte_admin user is also a user at database level and exists as a user in my user table udt_user_detail.

During the build and deployment I am using variables and they are set to dte_admin username and password.

My question is then, why does this fail with this user? Deployment Pipeline result

question from:https://stackoverflow.com/questions/65885576/executing-sql-script-causes-error-in-procedure-called-by-trigger

Answer 1

Waitting for answers