Excel VBA updating SQL Table - permissions issues

Question

I am connecting to a SQL table via an Excel VBA script using using SQL Server vs Windows Authentication as we're creating a generic update application to be used by several different users. If I log into SQL Server Management Studio with the generic ID/pswd and SQL Server Authentication, I can successfully insert and delete rows. When I use the VBA script, I receive an error indicating that the INSERT or DELETE permission was denied. The VBA script works successfully against the same table in a different schema, but fails going against this schema. I am assuming it's something in the SQL environment, as it seems like it's forced to Windows Authentication regardless of how I make the connection. Any help would be greatly appreciated.

Updates:

Thanks for the responses. I hope this additional information helps. We are on SQL Server 15.04102.2, and I am using SQL Server Authentication with a generic application ID and PSWD that has update permissions (my individual windows account only has select permissions)

As I indicated in my initial post, if I execute this code against one test environment, the DELETE and INSERT work perfectly. When I execute it against a different environment, the connection is made, but I get the permissions error on the DELETE and INSERT. If I use the exact same credentials and use SQL Server Authentication to go directly to that environment, I can successfully execute the DELETE and INSERT statements.

Here is the error I receive on the DELETE statement. A similar error is received on the INSERT statement if I bypass the delete for a new add.

Error -2147217911 (The DELETE permission was denied on the object ‘BCG_CGS_DETAILS’, database ‘xxxxxxcustom’, schema ‘dbo’.) in procedure Export CGS to SQL. sSQL = delete dbo.BCG_CGS_DETAILS where [CGSNUM]= ‘CGS-xxxxx’ AND [PDPD_ID]= ‘xxxxx’ AND EFF_DT=’7/1/2021’

Here is the code I am executing.

      Sub Export_CGS_to_SQL()
      
      Dim cnn
      Dim rst
      Dim sSQL As String
      Dim iRow As Integer
      
      Dim sLINE, sCategory, sBCBSNC_Medical_Policy, sBCBSNC_Standard_Provisions As String
      Dim sIn_Network_VALUE, sIn_Network_Type_of_payment, sIn_Network_Detail As String
      Dim sOut_of_Network_VALUE, sOut_of_Network_Type_of_payment, sOut_of_Network_Detail As String
      Dim sCustomized, sCGSNUM, sPDPD_ID, sEFF_DT As String
      
      Dim lLastrow As Long
         
      Dim sServer, sTable, sDatabase, sConnection As String
      
      On Error GoTo errHandler
   
      With Sheets("CGS Data")
      
         sCGSNUM = .Cells(2, 12)
         sPDPD_ID = .Cells(2, 13)
         sEFF_DT = .Cells(2, 14)
         
         If sCGSNUM = "" Or sPDPD_ID = "0" Or sEFF_DT = "" Then
            MsgBox "One or more required fields is blank.  Please make sure that the Product ID and Effective date are set correctly on the MISC tab, and that the CGS Number exists for row A06c on the Client Profile tab"
            Exit Sub
         End If
               
         lLastrow = Cells(Rows.Count, 1).End(xlUp).Row
              
         sServer = Worksheets("MACRO_DATA").Range("B6").Value
         sTable = Worksheets("MACRO_DATA").Range("B7").Value
         sDatabase = Worksheets("MACRO_DATA").Range("B8").Value
         sConnection = Worksheets("MACRO_DATA").Range("F5").Value
              
         'Create a new Connection object
         Set cnn = CreateObject("ADODB.Connection")
         Set rst = CreateObject("ADODB.Recordset")
         
         If cnn.State <> 1 Then
            sSQL = "Provider=SQLOLEDB;Data Source=" & sServer & "; Initial Catalog=" & sDatabase & ";" & sConnection & "; Trusted_Connection=yes"
            cnn.Open (sSQL)
         End If
   
         Set rst.ActiveConnection = cnn
         
         sSQL = "delete " & sTable & " where [CGSNUM]= '" & sCGSNUM & "' AND [PDPD_ID]= '" & sPDPD_ID & "' AND EFF_DT= '" & sEFF_DT & "'"
         cnn.Execute sSQL
         
         For iRow = 2 To lLastrow
         
            sLINE = .Cells(iRow, 1)
            If sLINE <> "" Then
               sCategory = .Cells(iRow, 2)
               sBCBSNC_Medical_Policy = .Cells(iRow, 3)
               sBCBSNC_Standard_Provisions = .Cells(iRow, 4)
               sIn_Network_VALUE = .Cells(iRow, 5)
               sIn_Network_Type_of_payment = .Cells(iRow, 6)
               sIn_Network_Detail = .Cells(iRow, 7)
               sOut_of_Network_VALUE = .Cells(iRow, 8)
               sOut_of_Network_Type_of_payment = .Cells(iRow, 9)
               sOut_of_Network_Detail = .Cells(iRow, 10)
               sCustomized = .Cells(iRow, 11)
               sCGSNUM = .Cells(iRow, 12)
               sPDPD_ID = .Cells(iRow, 13)
               sEFF_DT = .Cells(iRow, 14)
                     
               'insert row into sDatabase
               sSQL = "insert into " & sTable & "([LINE],[Category],[BCBSNC Medical Policy],[BCBSNC Standard Provisions],[In-Network_VALUE]," _
               & "[In-Network_Type of payment],[In-Network Detail],[Out-of-Network_VALUE],[Out-of-Network_Type of payment],[Out-of-Network_Detail]," _
               & "[Customized],[CGSNUM],[PDPD_ID],[EFF_DT])" _
               & "values ('" & sLINE & "', '" & sCategory & "', '" & sBCBSNC_Medical_Policy & "', '" & sBCBSNC_Standard_Provisions & "', '" _
               & sIn_Network_VALUE & "', '" & sIn_Network_Type_of_payment & "', '" & sIn_Network_Detail & "', '" & sOut_of_Network_VALUE & "', '" _
               & sOut_of_Network_Type_of_payment & "', '" & sOut_of_Network_Detail & "', '" & sCustomized & "', '" & sCGSNUM & "', '" _
               & sPDPD_ID & "', '" & sEFF_DT & "')"
      
               cnn.Execute sSQL
            End If
            
         Next iRow
              
         MsgBox "CGS Data successfully exported to " & sTable, vbInformation
              
         cnn.Close
         Set cnn = Nothing
      
      End With
      
   Exit Sub
   
errHandler:
      MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Export CGS to SQL. sSQL = " & sSQL
      cnn.Close
      Set cnn = Nothing
   End Sub