In the past we have explained
how LOGbinder for SharePoint uses SQL privileges. We also informed you about the unfortunate
workaround of giving dbo access to certain DB's in SQL in the sporadic cases when the SharePoint API interferes with access to the databases.
This was never a "workaround" that we were really happy with. Giving dbo access is not only like giving the bank the title to your home as collateral for the mortgage but also giving them a letter that says "Stop by anytime you want and while you're here feel free to repaint the walls and help yourself to the scotch in the pantry."
Thankfully, we have found a proper workaround that does not require dbo access. There is a role on the SharePoint SQL DB's named "SPDataAccess". We have found that giving the service account this role grants enough access for LOGbinder for SharePoint to function properly. Again we would like to specify that this is not the standard configuration needed with LOGbinder for SharePoint. This is only used in the rare situations when the SharePoint API is giving issues with DB access. For most of our customers the permissions set within SharePoint itself for the service account is all that is needed.
There are two ways to give the service account this role. One is using the SharePoint Management Shell and the other is directly in SQL (in our example below using SSMS).
Our preferred method is making the changes directly in SQL. We noticed that when using the SP Management Shell an extra role is given. We also noticed that this is not always the case as well. Sometimes the extra role is given and sometimes it is not. Why? We don't know. Maybe it's a hidden Microsoft feature.
Here is how to make the changes using SSMS.
1. In SSMS add your service account as a login.
2. Open the logins properties and locate the three databases that your SharePoint farm is using for the Admin Content, Configuartion and WSS Content databases.. In this instance we have SharePoint_AdminContent(GUID), SharePoint_Config2019 and WSS_Content(GUID).
3. For each database map the SPDataAccess role to the login. You will notice that for the WSS_Content db, after saving the role change SSMS also grants the PSDataAccess and the PSReportingSchemaAdmin role. If you have more than one content db, then you will have to perform these steps on all applicable db's with the WSS_Content prefix. For more information on how to set SPDataAccess on a large number of content databases,
click here.
You can also perform the steps above with a simple cmdlet using the SharePoint Management Shell. Run the following cmdlet:
Get-SPContentDatabase | Add-SPShellAdmin -UserName domain\ServiceAccount
So in our example below we ran "Get-SPContentDatabase | Add-SPShellAdmin -UserName lab\sp2019srvacct". Notice that doing this grants an additional role on all three databases; the SharePoint_Shell_Access role. As security experts our recommendation is obviously whichever process results in the least privilege needed to get the job done which, in this case, is making the changes via SSMS.
What does the SPDataAccess role allow? According to TechNet, the SP_DATA_ACCESS role will have the following permissions:
- Grant EXECUTE or SELECT on all SharePoint stored procedures and functions
- Grant SELECT on all SharePoint tables
- Grant EXECUTE on User-defined type where schema is dbo
- Grant INSERT on AllUserDataJunctions table
- Grant UPDATE on Sites view
- Grant UPDATE on UserData view
- Grant UPDATE on AllUserData table
- Grant INSERT and DELETE on NameValuePair tables
- Grant create table permission