The EXECUTE permission was denied on the object ‘sp_tables_info_90_rowset_64’

When trying to create a linked server and executing a “select * from” I recieved the following error:

OLE DB provider “SQLNCLI11” for linked server “SERVERNAME” returned message “Unspecified error”.
Msg 229, Level 14, State 5, Procedure sp_tables_info_90_rowset_64, Line 1
The EXECUTE permission was denied on the object ‘sp_tables_info_90_rowset_64’, database ‘mssqlsystemresource’, schema ‘sys’.

Seems like the provider SQLNCLI11 doesn’t grant you the proper rights on the masterdatabase to execute the SP sp_tables_info_90_rowset_64

Used the following query to connect the linkedserver, all works fine after that.

EXEC master.dbo.sp_addlinkedserver
@server = 'SERVERNAME',
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=SERVERNAME;UID=USER;PWD=PASSWORD;'

With linked servers you can use Four Part Naming Convention:

[SERVER].[DATABASE].[SCHEMA].[OBJECT]

So a query would look like:

SELECT * FROM [SERVERNAME].testdb.dbo.test

For more information about Linked Servers see this YouTube Video

Leave a Reply

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