When all settings are made, such as creating a kind of external content and use it, we created an external list, you can see this error. You may feel surprise that could be used administrator account to create a third type of site content, and access to a site collection administrator and still see the error message. Eh?
Here we must remember one thing the access denied error comes from the fact that the data source we are trying to connect an external one is not for SharePoint. :) So, he does not care what your role or permission in SharePoint. The external data source does not care if you want to perform a specific action that the user has access to do so or not. Thus, the user performs these operations [select, insert, delete, update] should have access to an external data source.
. Open Central Administration site
. Go to Manage service applications –> Select Business Connectivity Application
On this page you see all your external content types you added. [Don't forget to select the External Content Type option from top dropdown]
Hover the external content type and from the context menu select Set Permissions
It will open a dialog and there we have to give specific permission to the user. In my case I logged in as me and added that account and given all access.
Now, go back to your site and refresh the page. You start seeing all the data is pulled from Sql Server to the SharePoint 2010 external list.