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

Advertisements

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

  1. mangesh June 26, 2012 / 7:30 pm

    it’s work

  2. gannicus February 11, 2013 / 7:58 pm

    Great work! It’s working! Very good article!
    Greetings from Poland (Europe) 🙂

  3. Sreekrishna Palaparthi August 26, 2013 / 8:56 am

    Thanks for the tip… it’s working like a charm 🙂

  4. Jejoo August 30, 2013 / 4:15 am

    Thanks for the tip

  5. rahimah November 7, 2013 / 12:48 am

    awesome article..solve my issue big time..

  6. Tiago November 19, 2013 / 7:51 am

    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

  7. sreekanth bandarla November 19, 2013 / 10:23 am

    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)

  8. manoj October 2, 2014 / 7:35 am

    thanks, did work perfectly well

  9. Joe June 2, 2015 / 12:26 pm

    worked for me also!

  10. Steph June 24, 2015 / 11:16 am

    thanks, did work perfectly

  11. Gobinda Sharma November 20, 2015 / 3:56 am

    Thanks, worked for me as well..!!

  12. Sanjiv December 12, 2015 / 4:32 am

    good

  13. prashantd January 15, 2016 / 6:28 am

    Thank you so much for sharing information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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