2

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.

2 Answers 2

0

After another month of testing this at multiple sites, we determined that the solution is in fact to have our users check the "Keep me signed in" box on the Microsoft sign in page in all the browsers they use (although it's probably enough to have them do it in IE or their default browser).

0

Try:

Dim a As New MSXML2.ServerXMLHTTP60

a.Open "GET", url, False

Be sure to add reference to "Microsoft XML, v6.0"

2
  • Thanks for the reply, but that's literally what my code already does.
    – phrebh
    Oct 31, 2017 at 18:25
  • He's already doing that, but using "late binding" so that each user doesn't have to add the reference. Aug 11, 2020 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.