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?
Best Answer
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 theddl_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)