By: Arjun Chakraborty
If you are reading this article, I bet it’s because you ran into an error while viewing a SharePoint External List. Here are the 3 errors I most commonly run into, and their fixes:
1. User does not have permissions in the BCS content type:
If you see this error in the External List, associated with the external content type, then that means you have to give the current user permission in the BCS service application.
Go to Central Administration -> Application Management -> Manage Service Applications. Find the Service Application for BCS, and click on Manage.
Select the BCS content type, and then press Set Object Permissions. Then, add that user (or better yet, an AD group which has all the necessary users), and give it the ‘Execute’ permission.
========== OR ===========
Instead of selecting the Content Type, simply press Set Metadata Store Permissions. Then, add the user (or group), and make sure to check Propagate permissions to all BCS Models, … If you have multiple content types, all with the same target audience, then this is the best idea. However, only do this if you know the other BCS content types will not have their own unique permission settings. Going forward, all new external content types will also use this user-permission configuration.
2. The End User or BCS User does not have permissions on the SQL Server/database:
If you are seeing these errors in the External List, then there are permissions missing on the SQL server. If the external content type is set to use the user’s credentials, then the user does not have read/write access to the database (write is not necessary if only Read List and Read Item operations are used in the content type). If the external content type is set to use a secure store service account (more typical), then the account stored in secure store service does not have permission on the SQL database.
To fix this, simply give the mentioned user (in this case, “Dev\test02”) the necessary permissions on the database, or re-enter the credentials used by the SSS account.
3. The user does not have permission to use the Secure Store Service account:
This is similar to (1), but instead of the BCS, the user needs permissions on the Secure Store Service account entry.
Go to Central Administration -> Application Management -> Manage Service Applications. Select the Secure Store Service, and press manage:
Select the secure store account you are using with the BCS connection, and press edit:
Keep pressing next until you reach this screen:
You need to add the end user to the Members section (bottom). Better yet, add an AD group that has all the users who need to see the external list:
Press OK to finish.
You can also see who has rights to use this SSS account by selecting the account and pressing the Set Credentials button.
Hope that helps.
By: Arjun Chakraborty