Friday, June 13, 2014

SharePoint 2013: Insufficient SQL database permissions for user

Problem

You see the following critical error in a SharePoint Server 2013 farm server hosting a Search service query processing component, occuring at about 13-hour intervals:
Log Name:      Application
Source:        Microsoft-SharePoint Products-SharePoint Foundation
Date:          [date/time]
Event ID:      5214
Task Category: Database
Level:         Critical
Keywords:      
User:          [Search service account]
Computer:      [A query processing host server, eg WFE]
Description:
Insufficient SQL database permissions for user 'Name: [Search service 
account] SID: [SID] ImpersonationLevel: None' in database 
'[FarmConfigurationDB]' on SQL Server instance '[SQL Server Name]'. 
Additional error information from SQL Server is included below.

The EXECUTE permission was denied on the object 'proc_GetTimerRunningJobs', 
database '[FarmConfigurationDB]', schema 'dbo'.
Event Xml:
...
Solution

There are several ways of granting the search service account the necessary permission: granting it the SPDataAccess role to the database; adding the WSS_Content_Application_Pools Database Role to proc_GetTimerRunningJobs and granting that role the Execute permission; and granting the service account the DBO role for the configuration database.  These methods are presented here.  These approaches require that you remote into your farm's SQL Server host and launch SQL Server Management Studio.
  1. Adding the SPDataAccess role:
    1. In Object Explorer, expand the Security node, and then double-click on the search service account (eg, spSearch).  The Login Properties dialog appears.
    2. In the Select a page navigation panel, select User Mapping.
    3. In the Users mapped to this login panel, select the farm configuration database (eg, farm_Config).
    4. Enable the SPDataAccess role
    5. Click OK.
  2. Add WSS_Content_Application_Pools role to proc_GetTimerRunningJobs:
    1. In Object Explorer, expand the Databases node, and then look for the farm configuration database (eg, farm_config).
    2. Under this database node, expand Programmability > Stored Procedures.
    3. Under the Stored Procedures node, scroll down until you find dbo.proc_GetTimerRunningJobs.
    4. Right-click on this object, and then select Properties.  The Stored Procedure Properties dialog appears.
    5. In the Select a page frame, select Permissions.
    6. Click the Search button.  The Select Users or Roles dialog appears.
    7. Enter WSS_Content_Application_Pools and then click the Check Names button.  Brackets should appear around what you entered, indicating that this object name was recognized.
    8. Click OK. The Select Users or Roles dialog closes.  You will see this new role now appear under Users or roles.
    9. Select the WSS_Content_Application_Pools role.
    10. Below, in the Permissions for WSS_Content_Application_Pools panel, enable Grant for the Execute permission.
    11. Click OK.
    12. Exit SQL Server management Studio.
  3. Grant DBO role
    1. Login to the farm's SQL Server instance as administrator.
    2. Launch SQL Server Management Studio and connect to the farm database server.
    3. In the Object Explore pane at left, expand Security, then Logins, and then look for the search service account (eg, spSearch).
    4. Double-click this item.
    5. In the Select a page panel, select User Mapping.
    6. In the Users mapped to this login panel (upper one), select the farm configuration database.
    7. In the Database role membership for panel (lower one), check db_owner.
    8. Click OK.
References
  1. Event ID 5214 (Windows SharePoint Services health model)
  2. Insufficient SQL Server database permissions - Event 5214 (SharePoint 2010 Products)
  3. Search account got - Insufficient sql database permissions for user. EXECUTE permission was denied on the object proc_Gettimerrunningjobs
  4. EXECUTE permission denied on SharePoint Config DB
  5. My Sites and The SPDataAccess SQL Role
  6. Account permissions and security settings in SharePoint 2013
Notes
  • It's unclear to me at this point, which method (1 or 2) is better.  Reading reference [3] would seem to indicate to indicate the second method.  But reference [5] raises an intriguing alternative.  More generally, it's unclear from existing Microsoft documentation (namely, reference [6]), what role the search service account should have with respect to the farm configuration database and the proc_GetTimerRunningJobs stored procedure specifically.  Clarification from Microsoft on what it intended here would be helpful.
  • This posting assumes a farm having a single SQL Server instance.

No comments: