Inside of our SQL Server, we have created a linked server to an Oracle server. We ave mapped several users to the Oracle server and specified the credentials. On the Linked Server 'Security' properties, it says, "For a login not defined in the list above, connections will:" and we have selected "Be made using this security context:" and specified an Oracle user with read-only access.
Now, when we open SQL Server Management Studio as a user that is not explicitly specified in the list defined for the Oracle linked server, it uses the credentials with read-only access to connect. However, when we try to expand 'Catalogs' under the linked Oracle server, we receive the error:
The EXECUTE permission was denied on the object 'xp_prop_oledb_provider'