The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’

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

18 thoughts on “The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’”

  6. Hi there,

    And what the implications of giving access to this SP on a system database?
    What does the SP behind the scenes ?

    Cheers,
    Tiago

    Reply

  7. AFAIK, no negative implications at the Instance level and it has only to deal with SSMS. If you are using T-SQL for querying your Linked Server, you shouldn’t even get this error message(You will get this annoying message when you try to access the linked server catalogs via SSMS)

    Reply
  15. Pingback: Linked servers SQL to Oracle – SQLDREAMS

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.