SQL Server Linked Servers and Remote Statistics

Question

I'm trying to get a better understanding of how SQL Server uses statistics on remote SQL Servers (e.g. a Linked Server). I understand that the user needs db_owner/db_ddladmin rights to be able to use the stats on the remote box. At first this seems really straight forward based on the below text from Microsoft.

To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.

The pieces that is confusing me is the word "User". We use a standard security context for all connections rather than the user's login. This login/user has the needed permissions as defined above over the databases in question. Additionally, we do not use Impersonation.

I have tried running queries and capturing the traces on both ends using accounts with and without elevated permissions on the remove server. It looks to me like the remote server is not collecting statistics unless the actual user (the guy at the keyboard) has the elevated rights. I believe this to be true because I am only seeing calls to sys.sp_table_statistics2_rowset when the user has the needed rights.

I really hope this makes sense.

Does anyone have an understanding of how this really works in SQL 2008 R2 and below?

Answer 1

What I do is this 'trick':

Give this 'linkuser' read, whatever else needed and ddl_admin privileges, then run the following script to ensure that none of the ddl_admin privileges can actually be used.

But by being technically member of ddl_admin the user can now see statistics in the remote db! (note: from SQL2012SP1 there is no need for this hack, the stats are visible as they should be)

DENY CREATE TABLE TO [LinkUser]
DENY CREATE VIEW TO [LinkUser]
DENY CREATE PROCEDURE TO [LinkUser]
DENY CREATE FUNCTION TO [LinkUser]
DENY CREATE RULE TO [LinkUser]
DENY CREATE DEFAULT TO [LinkUser]
DENY CREATE TYPE TO [LinkUser]
DENY CREATE ASSEMBLY TO [LinkUser]
DENY CREATE XML SCHEMA COLLECTION TO [LinkUser]
DENY CREATE SCHEMA TO [LinkUser]
DENY CREATE SYNONYM TO [LinkUser]
DENY CREATE AGGREGATE TO [LinkUser]
DENY CREATE MESSAGE TYPE TO [LinkUser]
DENY CREATE SERVICE TO [LinkUser]
DENY CREATE CONTRACT TO [LinkUser]
DENY CREATE REMOTE SERVICE BINDING TO [LinkUser]
DENY CREATE ROUTE TO [LinkUser]
DENY CREATE QUEUE TO [LinkUser]
DENY CREATE SYMMETRIC KEY TO [LinkUser]
DENY CREATE ASYMMETRIC KEY TO [LinkUser]
DENY CREATE FULLTEXT CATALOG TO [LinkUser]
DENY CREATE CERTIFICATE TO [LinkUser]
DENY CREATE DATABASE DDL EVENT NOTIFICATION TO [LinkUser]
DENY CHECKPOINT TO [LinkUser]
DENY ALTER ANY SCHEMA TO [LinkUser]
DENY ALTER ANY ASSEMBLY TO [LinkUser]
DENY ALTER ANY DATASPACE TO [LinkUser]
DENY ALTER ANY MESSAGE TYPE TO [LinkUser]
DENY ALTER ANY CONTRACT TO [LinkUser]
DENY ALTER ANY SERVICE TO [LinkUser]
DENY ALTER ANY REMOTE SERVICE BINDING TO [LinkUser]
DENY ALTER ANY ROUTE TO [LinkUser]
DENY ALTER ANY FULLTEXT CATALOG TO [LinkUser]
DENY ALTER ANY SYMMETRIC KEY TO [LinkUser]
DENY ALTER ANY ASYMMETRIC KEY TO [LinkUser]
DENY ALTER ANY CERTIFICATE TO [LinkUser]
DENY REFERENCES TO [LinkUser]
DENY ALTER ANY DATABASE DDL TRIGGER TO [LinkUser]
DENY ALTER ANY DATABASE EVENT NOTIFICATION TO [LinkUser]

Answer 2

Well, it really depends how you set up the security context of the linked server, right? If the guy at sitting at the keyboard doesn't have any rights on the remote server, then how is the connection made: will it cut it off, will it use the current security context, or will it impersonate a particular login on the remote server? I think this is where you need to focus your attention. In our PROD environment, we have set up a user that has read, write and ddl admin privileges and all the connections are made using the security context of that remote login/password. I hope this helps.