SQL – Linked Server xp_prop_oledb_provider error

 

When you create a Linked Server on SQL Server, some users may get this error:
EXECUTE denied on object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’.

image

 

So, the only thing you need (if you have set up your permissions correctly on Linked Server) is to run the following code:

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO LOGIN;

If you use domain user, change login to [DOMAIN\username]

 

And this should solve your problem.

 

Good Luck

2 thoughts on “SQL – Linked Server xp_prop_oledb_provider error

  1. Graeme

    Luka,

    Thanks for the info, however this is only OK if you have 1 or 2 users. I have a team of 20 users that all need to be able to see the catalog of tables on the linked server. I am using a single user account for the link to which everyone maps to by default. All the team members are members of a database role, but I can’t find a way to give the role execute permission on this procedure. Is there any way to do this? I don’t want to have to grant access 20 times, and then remember to do it everyt ime a new person joins the team. Surely, that is why we have roles.

    Regards,

    Graeme
    (Melbourne, Australia)

    Reply
    1. lukagros Post author

      If you are on domain, create group in AD, put your users inside this group. Add this group to SQL server, and then run the query with login=domain\groupname

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>