Today in the morning I was contacted by my fellow DBA to look into an issue with a linked Server he setup from SQL Server to Oracle. He did setup a linked server from SQL Server 2005 to an Oracle database and tested successfully(From SSMS). Well, everything appeared to be okay, but the app teams are unable to use this Linked server.
The DBA tried expanding catalogs->default and Oops…he is receiving this weird message.
This looks like missing permissions issue at Oracle end, but wait……..he was able to test the linked server successfully! So what did he miss?
The answer is “Allow Inprocess” for OraOLEDB.Oracle provider. Once I asked him to check that box, Voilaaaa….it works 🙂
For more information on the properties of these providers, please see http://technet.microsoft.com/en-US/library/ms188095%28v=sql.90%29.aspx
Happy Friday all!
MSSQLTREK 
Run Sql Management Studio as administrator worked for me