Today I was setting up a Linked Server from one SQL Server 2008R2 Instance to another SQL Server 2005 Instance with one of the Databases as a Catalog and I was able to create the Linked Server and Tested it successfully, but the Developer was getting an annoying Error as shown below…
The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider)
What does it mean? It is complaining about a missing permission from Resource Database, which really can’t be touched by any means in terms of Permissions from our SSMS. ( Remember, Resource DB can be accessed just from File System as mdf and ldf files from your Binaries Folder)
So, how to fix this annoying Error?
My Scenario:
1. I’ve a created a Linked Server using “Microsoft OLEDB Provider for SQL Server” as Provider by choosing Server type as Other Provider instead of going with SQL Server. Don’t ask me why 😀
2. I’ve logged in as a user which is not a SysAdmin and when i try to expand the Catalogs from Linked Server I got this below error message, as you can see in below Screenshot.
Please note I’ve logged in using login “Linked” – I’ve created a test login called “Linked” for this Demo Purpose…
How to Fix this?
1.Map the User to MASTER Database( By Def. It will be added to Public Role)
2.USE MASTER
GO
GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO LOGIN;
Once, I did this, I’m able to access the Linked Server without any Issues as you can see below…
Howdyyyyyyy! Hope this helps
Leave a comment