Recently we added Access Services to our SharePoint 2013 environment. After the initial setup we started testing the new implementation and stumbled over an error while accessing the details page of an app. This has nothing to do with access itself, more with apps in general.
After clicking on details you get an error message with the correlation id. So nothing easier to get the log regarding that error.
SqlError: ‘The EXECUTE permission was denied on the object ‘prc_CountAppInstanceData’, database ‘SharePoint_Logging’, schema ‘dbo’.’ Source: ‘.Net SqlClient Data Provider’ Number: 229 State: 5 Class: 14 Procedure: ‘prc_CountAppInstanceData’ LineNumber: 1 Server: ‘Database.fqdn.com’ f2ec3e9d-ac28-80f1-8d80-1d89f5d672a9
11/06/2015 12:50:34.93 w3wp.exe (Servername.fqdn.com:0x5DE4) 0x5D34 SharePoint Foundation Database 5214 Critical Insufficient SQL database permissions for user ‘Name: NT AUTHORITY\IUSR SID: S-1-5-17 ImpersonationLevel: Impersonation’ in database ‘SharePoint_Logging’ on SQL Server instance ‘Database.fqdn.com’. Additional error information from SQL Server is included below. The EXECUTE permission was denied on the object ‘prc_CountAppInstanceData’, database ‘SharePoint_Logging’, schema ‘dbo’. f2ec3e9d-ac28-80f1-8d80-1d89f5d672a9
This leads us to a permission issue. We tried granting the App Management Service Account “SPDataAccess” permission on our logging database, which did not help.
As you can see in the log there is an user specified IUSR SID: S-1-5-17. This leads to a permission issue from IIS WebServer. Makes sense as all SharePoint Applications are running within the IIS and their Application Pools. As our App Management Service Account wasn’t the issue we granted our Application Pool Account under which the Web Applications are running, the “SPDataAccess” permission on our logging database.
Et voila, this fixed the issue.
Problem: App Details do not work.
Solution: Grant SPDataAccess Permisson to your Web Application Pool Account on your SharePoint Logging Database.