SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

EXECUTE permission was denied on the object xp_prop_oledb_provider

SQL Server database administrators create Linked Server on SQL Server instance but sometimes SQL database users can not display tables and views list under linked server definition. If SQL programmers try to extend the Catalogs node, SQL engine raises the exception "The EXECUTE permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys'."

Using server admin, I have created a number linked servers to enable SQL developers to fetch data from other data platforms like an Amazon Redshift database or from SAP HANA database.

When the SQL developers try to list the tables and views under the linked servers created on SQL Server by trying to drill-down the Catalogs node as follows;

display catalog details under SQL Server Linked Server

SQL developers experienced following error message:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception eccurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The EXECUTE permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

The error is infact including its solution and suggests to give execute permission on xp_prop_oledb_provider object.
On master database, I execute below "grant" command using database administrator to give the required execute permission on SQL database object xp_prop_oledb_provider

grant execute on xp_prop_oledb_provider to pcs

Unfortunately I got following error indicating that the database user "pcs" does not exist in master database.

Msg 15151, Level 16, State 1, Line 1
Cannot find the user 'pcs', because it does not exist or you do not have permission.

Then I execute the same SQL grant command on the database where the user exists

grant execute on xp_prop_oledb_provider to pcs

But I see that it did not help. This time error changed to

Msg 4629, Level 16, State 10, Line 1
Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.

This error was expected in fact. Because such permissions on system procedures should be given to database users on master database

When I checked the master database users with an admin account, I could not see the PCS user in the list of master database. One of the above errors was thrown because the related user does not exist in maser database.
To resolve this issue, using SQL Server instance Logins screen under Security tab,

SQL Server instance Logins

I displayed the Properties screen of the target SQL Server login user using the context menu options. When you switch to User Mappings tab from right menu, you will see that there is not a mapping enabled for the master database. If this is the case for you too, please mark the checkbox under Map column as you can see in following screenshot. Database developers will realize that the user has public role on master database.

map SQL Server login to master database in public role

After this mapping is done, the user is now created on master daabase automatically. This means I can once more execute "Grant" SQL command on master database to give required "Execute" permission on database object or extended procedure xp_prop_oledb_provider

grant execute on xp_prop_oledb_provider to pcs

Now the SQL command is executed successfully without any SQL exception. And the user can now drill-down through the nodes under Linked Server definition for catalog tables and views.

display tables and views under SQL Server Linked Server definition

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.