"EXECUTE permissions denied on object" error when trying to add, analyze or restore a backup (4324765)

When trying to add, analyze or restore a SQL backup, the following error is thrown:

"EXECUTE permissions denied on object 'xp_objectrecovery_executeselect', database 'master', schema 'dbo'"

The Recovery Manager for SharePoint service account does not have permissions to execute the SQL stored procedures. There has also been an issue where the RMSP service account was given correct DB access, but accidently a local SQL account was created and granted the stored proc permissions. In this case, that created account must be removed and the correct Windows account must be granted correct permissions.

WORKAROUND:

Add the explicit 'execute' permissions on the extended stored procedures for the Recovery Manager for SharePoint service account on all SharePoint Back-End SQL servers where the target SharePoint content databases and QMC_Repository database is located:

xp_restore_filelistonly
xp_restore_headeronly
xp_restore_setinfo
xp_sqllitespeed_version
xp_objectrecovery_executeselect
xp_objectrecovery_viewcontents

If you are working with LiteSpeed backups, the following procs also need 'execute':
xp_restore_database
xp_restore_filelistonly
xp_restore_headeronly
xp_restore_setinfo
xp_sqllitespeed_version

1. Open SQL Management Studio
2. Connect to the SQL server
3. Expand System Databases - master - Security - Users, add the Recovery Manager service account (if it not already added)
4. Open properties of the user and go to Securables
5. Click Search
6. Select Specific objects - Object Type - Extended Stored procedures:
   
   


7. Press Browse and check extended stored procedures specified above
8. Grant explicit Execute permissions for the selected stored procedures: