CODEWITHSUNDEEP
sql-serverlinked-serveropenquery
aragorn
aragorn

Reputation: 197

Direct linked server querying VS querying via openquery

In SQL Server Management Studio, I have created a linked server to another SQL Server. The name of the linked server that I created is "linkedserver". The linked server uses the Microsoft OLE DB provider. However, the problem described below occurs also with other providers.

In the remote SQL server there is a database [db] and a table [table] in the schema [schema]. [table] has two columns, [column] and [column2].

I have an SQL user 'user' in the remote SQL server which has only read rights, only for [column] but no for [column2]. The problem is that When I try to query data like the example below I get an error that I do not have access in [column2], although [column2] does not appear in the query. My work around is to create openqueries instead but this is not handy.

-- Fetch one column via the linked server - DOES NOT WORK
SELECT TOP 1 [column]
FROM [linkedserver].[db].[schema].[table]

The error message of the first failing query is:

Msg 2557, Level 16, State 7, Procedure sp_table_statistics2_rowset, Line 105
User 'user' does not have permission to run DBCC SHOW_STATISTICS for object '[db].[schema].[table]'.

Msg 230, Level 14, State 1, Procedure sp_table_statistics2_rowset, Line 105
The SELECT permission was denied on the column 'column2' of the object 'table', database 'db', schema 'schema'.

The workaround with openquery is:

-- Fetch one column via the linked server through an openquery - WORKS
SELECT *
FROM OpenQuery (linkedserver
'SELECT TOP 1 [column]
 FROM  [db].[schema].[table]')

Do you know how to overcome this, or why is this done? My assumption is that the first query tries to fetch all columns of [table] and the apply the filter in SQL Manager Studio. Am I right on it? Can I overcome this problem somehow?

Upvotes: 1

Views: 4611

Answers (1)

sepupic
sepupic

Reputation: 8687

When you execute your query on a linked server, the sp_table_statistics2_rowset is called on a linked server. You can execute 

 exec sp_helptext 'sp_table_statistics2_rowset'

to view the code and in cursor (line 105) you'll find this code:

dbcc show_statistics(@qtbl, @statname) with stat_header join density_vector

So for executing this sp the user on your linked server must have a permission to execute dbcc show_statistics, and this is not the same for different versions of SQL Server. Prior to SQL Server 2012 SP1 the requirement was "user must own the table or the user must be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role" and it was not the case in most of situations (when user is not db_owner on linked server), so the old providers eat this error silently and the query was optimized without using the statistics. SQL Server 2012 SP1 changed the behavior, the permissions required was changed like this:

SQL Server 2012 SP1 modifies the permission restrictions and allows users with SELECT permission to use this command. Note that the following requirements exist for SELECT permissions to be sufficient to run the command: + Users must have permissions on all columns in the statistics object Users must have permission on all columns in a filter condition (if one exists) The table cannot have a row-level security policy.

So I think your server version is >= 2012 SP1 and your user is not passed the requirements as he has no permissions on some columns. I cannot reproduce your issue as I have SQL Server 2008 R2 where your case just works well, so I can advise you to use an old db provider, create another linked server using SQLNCLI10 and test if the issue is gone. The link to check dbcc permissions: https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-show-statistics-transact-sql

This article says that you can turn off this behavior using TF9485, but I don't know if it will affect the required permissions for execute dbcc only or it also will affect the native client behavior as well

Upvotes: 3

Related Questions