Blog:Search Service Application stops working after adding a user to Administrators

I came across an interesting bug while trying to add a user the Administrators of a Search Service Application in SharePoint 2013. When I tried adding the user, and clicking OK, and error is returned: “User does not have permission to perform this action” along with a correlation ID. Further investigation in the ULS logs revealed that the problem was SQL permission related: “The EXECUTE permission was denied on the object ‘proc_MSS_GetConfigurationProperty’, database ‘SPSearch’, schema ‘dbo’.” Additionally performing a search fails and logs the error: “There was an exception in the Database. Please retry your operation and if the problem presists, contact an administrator.” (The error message has a typo too). 

After researching, it appeared that when adding a user to the Search Service Application Administrators using the web Interface, the SPSearchDBAdmin role is removed from all users, including the Search Service Account, for the search databases (Search, AnalyticsReportingStrore, CrawlStore, & LinkStore). This problem can be resolved by running the follow in SQL script:

USE SPSearch
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your search service account>’;
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your account to add>’;

USE SPSearch_AnalyticsReportingStore
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your search service account>’;
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your account to add>’;

USE SPSearch_CrawlStore
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your search service account>’;
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your account to add>’;

USE SPSearch_LinksStore
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your search service account>’;
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your account to add>’;

It has also determined that adding a user using PowerShell will not break the Search Service Application. Here is a sample script:

Add-PSSnapin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue

$administrator=”<Your account to add>”
$SearchServiceApplication = Get-SPEnterpriseSearchServiceApplication
$principal = New-SPClaimsPrincipal $administrator -IdentityType WindowsSamAccountName
$security = Get-SPServiceApplicationSecurity $SearchServiceApplication –Admin
Grant-SPObjectSecurity $security $principal “Full Control”
Set-SPServiceApplicationSecurity $SearchServiceApplication $security -Admin