Error while deploying Sharepoint 2013 timer job :The EXECUTE permission was denied on the object 'proc_putObjectTVP', database 'MSSQL', schema 'dbo'

Question

While trying to create a custom SharePoint timer job at feature activation I got the following error from the log files:

System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'proc_putObjectTVP', database 'MSSQL', schema 'dbo'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.Sql... 5c6d109c-dbc6-e02e-7ae4-010d7f559e0b

In order to make it work i located the stored procedure proc_putObjectTVP and granted execute permission to the site apppool userID. It worked as desired.

My question is:

Is this a bug in Sharepoint 2013? Is this the proper way to do it? (On production environment I may not be allowed by the server administrator to perform such operations)

Answer 1

I had a similar error in the event log for the account used for SharePoint 2013 services:

Insufficient SQL database permissions for user 'Name: XXXXX\SP_Services SID: xxxxxxxxxxxxxxx ImpersonationLevel: None' in database 'XXXX_Config' on SQL Server instance 'XXXXXXXXX'. Additional error information from SQL Server is included below.

The EXECUTE permission was denied on the object 'proc_putObjectTVP', database 'XXXX_Config', schema 'dbo'.

Googling around lots of blog posts recommend the same approach of applying the required permission to the stored proc. Personally I didn't like this approach, however I eventually found this TechNet post which grants the required permissions by adding the stored proc to the securables of the WSS_Content_Application_Pools role.

Using SQL Server Management Studio do the following:

1. Expand Databases then expand the SharePoint_Config Database.
2. Expand Security -> Roles -> Database Roles
3. Find WSS_Content_Application_Pools role, right click it, and select Properties
4. Click on Securables and click Search
5. Next click Specific objects and click OK
6. Click Object Types and select Stored Procedures. Click OK
7. Add the Stored Procedure 'proc_putObjectTVP' and click OK (if it does not automatically grant it exec permission; you need to click the checkbox on "execute" and save it)

Using this method any new accounts added to the WSS_Content_Application_Pools role will have the correct rights preventing the problem cropping up again.

Answer 2

SPDataAccess role in SharePoint_Config was configured to execute proc_putObjectTVP for my install of SharePoint 2013 (which has been a trial-by-fire to get used to SQL Server 2012), anyway, making sure my sharepoint users had that role set seems to have done the trick (and of course brought up more errors to debug, now that more things are successfully starting...)

SPDataAccess (also written as SP_DATA_ACCESS) has been a useful role to Google for, bringing up tons of good resources and tips to fix one problem or another. I'll be reading blogs all night. I suspect configuring databases is old hat for quite a few SharePoint admins and devs, but it's not as well-explained, particularly as the wizard does so much (and so little) for you.

I signed up for Safari Books just to access http://my.safaribooksonline.com/book/programming/microsoft-sharepoint/9781118655047 and books like it. It's useful to help me "think like SharePoint", though Google has been just as much help. (More, really.)