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

23 March,2020 by Jack Vamvas

Question: When attempting to expand the Catalogs in the linked server object defined,  I received below message.How can I fix this problem?

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

Answer: The overall fix is to grant execute rights to the logon requiring the privileges on SYS.XP_PROP_OLEDB_PROVIDER.

Step 1 >>  Map the user in the master database

USE [master]
GO
CREATE USER [myuser] FOR LOGIN [myuser]

Step 2 >> GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [myuser]

 

These are the basic steps. But quite often in an organizations SQL Server Security Policy   , direct execute rights on an objects are prohibited.  Normally a database role   may be required

 

Read more on sql server linked servers 

SQL Server Linked Servers and User permissions

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Error Message: The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer