The EXECUTE permission was denied on the object proc_EnumResourcesAtScope

http://kjellsj.blogspot.com/2011/02/help-content-cannot-be-displayed-in.html

[Alert] : SharePoint: Insufficient Permission To Connect To The SQL Database

[Alert Description] : A critical incident has occurred where services are not able to login to the database on server

Event ID: 5214

Event Description: Insufficient SQL database permissions for user 'Name: DOMAIN\Account SID: S-1-5-21-830005768-197582238-3774774025-5022 ImpersonationLevel: Impersonation' in database 'AdminContentDB' on SQL Server instance 'SQL Server'. Additional error information from SQL Server is included below.

The EXECUTE permission was denied on the object 'proc_EnumResourcesAtScope', database 'AdminContentDB', schema 'dbo'.

Solution:

After a iisreset this doesnt work anymore.

A solutions: locate the WSS_Content_Application_Pools role in the admin db database roles.

In the properties go to securables and search (add) the stored procedures proc_EnumResourcesAtScope and proc_EnumResourceValuesForAllLangs. Grant them execute permissions.

Event 6398 and 5586 SharePoint Foundation

By Andrew Toh On February 6, 2012 · Leave a Comment · In Administration

.

.

.

.

Problem
Event logs were getting filled with the following errors:
Event 5586, SharePoint Foundation
Unknown SQL Exception 2812 occurred. Additional error information from SQL Server is included below.
Could not find stored procedure ‘dbo.Search_GetRecentStats’.
Event 6398, SharePoint Foundation
The Execute method of job definition Microsoft.Office.Server.Search.Monitoring.HealthStatUpdateJobDefinition (ID 3ed3dbdc-bbc4-45b6-8e84-449cdd2997f4) threw an exception. More information is included below.
Could not find stored procedure ‘dbo.Search_GetRecentStats’.
Solution
I tried the following without any success (although it has worked for some).

1. Go to Central Administration -> Manage Service Applications -> Usage and Health Data Collection Service Application -> Uncheck everything. Restart the Timer Service. Re-check everything.
2. Recreate the Usage and Health Data Service Application. Delete the Service Application in Central Administration and re-create it using the PowerShell command: New-SPUsageApplication -name “Usage and Health Data Collection Service Application” -database server “dbserver” -databasename “dbname”.
3. Check that the Timer Job service account has sufficient permissions to the UsageAndHealthDataCollection database.

What worked:

1. Go to Central Administration -> Monitoring -> Review job definitions
2. Look for the job: Search Health Monitoring – Trace Events and see when it was last run. Chances are the job is set to ‘Disabled’ and never ran.
3. Enable the job and click on ‘Run now’.
4. The errors should stop appearing in the event log.

Cause
The Search Health Monitoring – Trace Events jobs creates the stored procedures in the UsageAndHealthDataCollection Database needed to run the job definition Microsoft.Office.Server.Search.Monitoring.HealthStatUpdateJobDefinition job.