While playing with LINQ to SQL, I faced a problem where generating a wrapper method for the Northwind.dbo.[Ten Most Expensive Products] stored procedure generated the method with a return type of int; It should have been ISingleResult<T> instead where T would be the type of the object which would contain a row returned from the procedure and ISingleResult<T> would of course be the enumerable object containing all of the rows returned by the procedure.
The following is the signature of the method generated by LINQ.
I had used a custom SQL Server user account northwind for generating the wrapper code; that user had the following rights on the Northwind database.
I searched on the Internet but could not find the solution to this problem, even on the MSDN LINQ forum located @ http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=123&SiteID=1
During diagnostics, I changed the connection string to use the system administrator id/password and regenerated the wrapper method just to see what happens; I was not surprised because this time around it generated the method signature as it should have been!
So that proved my suspicion that it has something to do with the security and the rights assigned to the user. Since the developers don't always have the admin rights on databases (may be some times on the development database, but that's pretty much it), hence the use of the system id/password is out of the equation.
Now the next task for me was to find out exactly what permissions are needed in order for the user account to be able to successfully generate the wrapper code.
Looking @ the database roles in SQL Server, the only other powerful role which could be assigned to the northwind user was db_owner!
Assigning that role to the user and regenerating the wrapper code for the procedure generates the correct code.
Looks like the user account for generating the database wrapper code in LINQ should be the db owner; having db_ddladmin role is not going to cut it.
I hope that helps.
Do let me know if that's not the case or you think otherwise.