If you have scripted your SSA, make sure the farm account has db_owner on your search db’s, or you may find yourself in a lot of trouble one day!
The above screenshot is from a live intranet for a 5,000+ employee company, and I have recently experienced similar issues twice at another customer (in a test environment fortunately), which led me to get to the bottom of this.
The live incident happened back in May and is the worst outcome of the issue I’m about to describe, where you get no search results at all. Less severe outcomes of this error is that you are not able to view your content sources, add new crawl rules, or perform other search related tasks. If you examine the ULS logs and Windows event logs you can see errors like this:
A database error occurred. Source: .Net SqlClient Data Provider Code: 229 occurred 0 time(s) Description: Error ordinal: 1 Message: The EXECUTE permission was denied on the object 'proc_MSS_GetConfigurationProperty', database 'Search_CrawlStore', schema 'dbo'
So for some reason the Search Service account has been revoked permissions on the search db’s.
This is what happens!
If you install your SharePoint farm using PowerShell commandlets (like AutoSPInstaller uses), then after the installation the Farm account will not have db_owner permissions on the Search DB and the Search Stores DB. The dbo user on the database will however be – which means if you scripted the install as the farm account, you are good to go, but you shouldn’t use the farm account for installs.
However, if you create the SSA via the UI, then the dbo user is the farm account, and you won’t experience this, but you have funky GUID db names.
If the farm account does not have db_owner rights and you go and give administrator permissions for a new user to the Search Service Application via Central Admin, then all users are revoked access from the search db’s. This is because the farm account is still able to remove users when missing the db_owner schema, but cannot re-add them. And all hell breaks loose!
Following the Tech article Account permissions and security settings in SharePoint 2013, the farm account should have db_owner for all SharePoint databases, so one could argue that there is a bug in how SharePoint creates the search databases when scripting.
This means that if you are scripting, make sure the farm account has db_owner on your search db’s, or you may find yourself in a lot of trouble one day!
Other references: http://blog.thewulph.com/?p=374