Search sqlserver-dba.com
Follow sqlserver-dba.com
For any SQL Server issue send email to: jack@sqlserver-dba.com
SQLServer-DBA.com Links
Recent Posts
- Get-ADGroup find AD groups with like
- Automate SQL Server Vulnerability Assessment
- How to create a PSCredential object
- The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’
- Extended Events -capture query errors
- SQL Server Docker and Container cheatsheet
- The DELETE statement conflicted with the REFERENCE constraint
- Email separator in Powershell
- Adding SSMS to the PATH variable with Powershell
- SQL Server Maximum Columns per table
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.
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.
Posted by: |