I've got a couple of hundred automations written in Excel that update a SharePoint list every time they're run. A small group of us can run the code and the list will update fine, but most users get some kind of access denied error. There are two user groups seeing the issue: owners and visitors. Most of the owners can update the list, but one cannot. Most of the visitors cannot update the list, but one can. That tells me that it isn't group permissions on the list in question.
I've rewritten the mechanism a few times, starting with ADO (which only worked by creating and running a VBS file on the fly), going to Lists.asmx, and finally ending up on REST calls. The issue happens no matter the technique. And because I'm using REST to get the list type for my POST call, I can see that the issue even happens on GET calls:
Private Function GetListType(listName As String) As String
Dim objXMLHTTP As Object
Dim entityType As String
entityType = "/_api/Web/Lists/getbytitle('" & listName & "')/ListItemEntityTypeFullName"
'create XML HTTP object
Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
With objXMLHTTP
'open connection to site
.Open "GET", SITEURL & entityType, False
.Send
'return value from results
GetListType = Mid$(.ResponseText, InStr(.ResponseText, "SP.Data"), _
InStrRev(.ResponseText, "<") - InStr(.ResponseText, "SP.Data"))
End With
End Function
When I run that function, I get the proper value to use for my list type in the metadata for my POST to create a list item. When one of the users with the issue tries it, the ResponseText comes back with (in part) "Access denied. You do not have permission to perform this action or access this resource."
In order to run any of the automations, they have to be downloaded from a library on the same site as the list being updated. So they are signed in when the REST calls happen. I've tried to have users check the box to keep signed in, but that doesn't help. They're not being asked to sign in, either (which is behavior I saw early on in my testing before I checked the box).
Both groups have "Use Remote Interfaces" checked. I have deleted a user from the owners group and readded her to the same group, but that didn't help. I do not have admin rights to the site and I cannot publish apps. I can't even see the Site App Permissions page. Creating an OAuth token using a passed username and password is a non-starter. It shouldn't be necessary since some of us can update the list from Excel without issues. I've also checked permissions for a few users that can and cannot use the REST calls and they are identical and given through the correct groups.
Any help would be appreciated.