THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: Linked server security configuration and how it can hurt you

If you change the security configuration of a linked server from using login UserA to login UserB, would you expect that to significantly impact the performance of a query that uses the linked server, assuming that both users have permission to select from the remote table referenced in the query?

 

Many people probably would answer no. After all, as long as the connection has the permission to access the remote table, how the connection is authenticated should not matter, right? Well, it’s more complicated than that.

 

Let’s say you have the following the query:

 

-- select @@servername returns NYCSQL01

SELECT o.*

  FROM orders o inner join NYCSQL02.sales.dbo.order_details od

        on o.OrderID =od.OrderID

WHERE o.OrderDate = '20090717'

 

In the above query, NYCSQL01 is the SQL Server instance where the query is being executed and NYCSQL02 is a linked server pointing to a separate SQL Server instance.

 

To process this query, SQL Server optimizer on NYCSQL01 may choose between two strategies (among others). First, it may decide to retrieve one row at a time from the table order_details on NYCSQL02 for the rows that match the OrderID values found on NYCSQL01 for the given OrderDate value. The second strategy is to retrieve all the rows of order_details from NYCSQL02 and then perform the join locally on NYCSQL01. Depending on the data distribution of the rows in order_details, either approach can be efficient.

 

To make the correct decision as to which strategy to use, NYCSQL01 needs the distribution statistics for table order_details from NYCSQL02. So far, so good. The question is what permission NYCSQL01 needs in order to retrieve the distribution statistics from NYCSQL02.

 

It turns out that it needs the same permission as is required to execute DBCC SHOW_STATISTICS. Per Books Online, the permissions for DBCC SHOW_STATISTICS are:

 

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.

 

This is where the problem lies. You may think that as long as you can access the table order_details on NYCSQL02, you are all set with the query above. After all, if you just want to retrieve some data from the order_details table, it would not be a security best practice to be given a sysadmin, db_owner, or even db_ddladmin role.

 

If the connection to NYCSQL02 is not the table owner, or a sysadmin, db_owner, or db_ddladmin, you’ll still get the result back. But the query optimizer on NYCSQL01 will not have the stats on order_details, and may just decide to do a table scan on order_details. If order_details turns out to be very large table, the performance of the above query is shot, or worse, it could drag NYCSQL01 down with it.

 

Whether it is reasonable to require sysadmin, db_owner, or db_ddladmin in order to process a distributed query efficiently is an issue for a different thread of discussion.

 

My gripe is that the exact permission requirements for efficiently processing a distributed query, especially a distributed join, is not well documented, if it is documented at all. Given its enormous risk, I’d argue that this should not only be documented, but rather it should be highlighted in bold in SQL Server Books Online.

 

If you agree, please go to Microsoft Connect and vote for the following item:

 

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=476001

 

Published Tuesday, July 21, 2009 2:16 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Michelle Ufford said:

Great article as always, Linchi!  Thank you for bringing this to attention.  

July 21, 2009 2:21 PM
 

Jack Corbett said:

Wow.  Didn't know it had that much impact.  At 1 job we had dedicated linked server account that we used for all linked servers that just was given datareader rights.  I'm sure this caused performance problems.

July 21, 2009 2:59 PM
 

Linchi Shea said:

From my first-hand and quite unpleasant expeience, I can state categorically that the datareader rights would not be enough. If you don't see any performance difference/impact, you are just being extremely lucky in that whatever default assumption the optimizer uses in the absence of the stats happens to be good enough.

July 21, 2009 3:10 PM
 

Chris Wood said:

Linchi,

We define our linked servers to have a Windows account or SQL login on the local server that runs the query thru the linked server map to a Windows account or SQL Login on the remote server. I am assuming that the remote login is the one that needs the special permission so it can retrieve the stats. So for us if the remote SQL login was REMOTE_SQL_LOGIN on server 2 it would need at least db_ddladmin rights rather then be in the public group and be given specific permission on the tables the query would want to use.

Chris

July 21, 2009 4:04 PM
 

Linchi Shea said:

Chris;

Whatever login that is used to access the remote linked sever needs to be able to run DBCC SHOW_STATISTICS. So yes, your REMOTE_SQL_LOGIN needs to be either the table owner, db_ddladmin, db_owner, or sysadmin. You can run Profiler trace to see what is being executed on the remote linked server. You'll see some internal procs being executed, and one of these internal proc will call DBCC SHOW_STATISTICS. Without the elevated permissions, the call will fail. If you include the Exception event in the trace, you'll see the failure.

July 21, 2009 4:30 PM
 

Linchi Shea said:

Many more have read this post than have voted on the item. Again, if you agree, please click on the above link to vote. If you don't agree, I'm interested in your feedback/comments as well.

July 21, 2009 4:38 PM
 

Michael Zilberstein said:

Wow, never thought about it.

Doesn't it mean that this user will experience same problems executing query directly on the server? If user doesn't have the required permissions, he\she won't be able to access statistics regardless of the way he\she connects to the server (directly or via linked server).

July 22, 2009 2:59 AM
 

yopispo said:

I'm agree with Michael: why a user doesn't need the same permission on local server to get a good execution plan on a common select?

July 22, 2009 3:40 AM
 

Linchi Shea said:

Michael and Yopispo;

On a local server, the optimizer has access to all the stats (if they are there) because it's part of the database engine. It does not need to be granted any permission. In a distributed join scenario, the optimizer (or a component on its behalf) of the local server must connect to the remote linked server to retrieve the stats. It needs to be authenticated and authoried on the remote linked server before it can do things there, including retrieving the stats. That's where the permission issue comes up. You can't just give this local server unrestricted access to the stats on the remote linked server. That would present a security issue.

July 22, 2009 7:00 AM
 

yopispo said:

Thanks, Linchi. It makes sense the needed for that permissions.

July 22, 2009 7:52 AM
 

Dm Unseen AKA M. Evers said:

This is not as straightforweard as it sounds. I use linked servers to connect to Oracle 10g instances, and I defnitly do not see my sqlserver translating DBCC SHOW_STATISTICS to an oracle equivalent statement. What MS fails to tell you that they fudged inter server metadata communication (it does work well for non sqlservers, and  they need the user connection to actually run the analysis from the master server! , which is very bad behaviour) And even then, it should be source agnostic. I do not know if OLEDB has an API for sharing this kind of information (maybe the MSDTC should handle this), but if not, MS should certainly look into providing one. Of course, If other database vendors will implement such an API for their database platform is another matter.

July 22, 2009 9:29 AM
 

Chris Wood said:

Linchi,

I gave Erland's https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=475804 a 5 as you should not need the greater permissions to run DBCC SHOW STATISTICS. You are right that it needs to be documented and so I have voted for your item too.

Chris

July 22, 2009 6:10 PM
 

ALZDBA said:

I knew there were stability issues with linked server usage, but didn't know this was one of the reasons.

Thank you for sharing.

Vote earned !

July 23, 2009 2:18 AM
 

Aaron Bertrand said:

I'm a day early; sorry. But I have a lot of interesting items to share this week. ========================================

July 24, 2009 9:46 AM
 

Sam Bendayan said:

Great article!  But I couldn't vote for it...I got this error...anyone else get this issue?

Page Not Found

The content that you requested cannot be found or you do not have permission to view it.

If you believe you have reached this page in error, click the Help link at the top of the page to report the issue and include this ID in your e-mail: 10ac89ce-ce5a-4296-801d-83677f7a26ba

July 24, 2009 4:59 PM
 

Dan said:

Good to know.

Thanks

Pingback from http://MadeBySQL.blogspot.com/

July 27, 2009 5:17 AM
 

Kevin Lloyd said:

Do you know (or tested) if OPENROWSET is affected by this as well?  Or does it simply use my SELECT permissions at the destination?  If it is not affected, as I would expect would be the case, it definitely seems like it should be something that is fixed.  Regardless of whether it is similar behavior, I would expect the execution behavior to be the same at the destination regardless of method used and not as you discovered.  I voted a 5 as well.

August 27, 2009 4:37 PM
 

Murat said:

We have two much lengthy blockings with the sp_table_statistics2_rowset system procs which are used to get statistics from linked servers on the background. I checked it. They work correctly and bring updated statistics when executed in query window.

We linked the servers with sa login, so DBBC Show_Statistics should not be an issue. Where should we check to identify the source of problem?

We have blocking on these system level procs.

Thanks

October 14, 2011 6:33 AM
 

Brainiac said:

Awsome... Nice piece .....

March 1, 2012 2:47 AM
 

Leonard said:

Are Orthodontists Basically Doctors?

ceramic braces

October 15, 2012 6:35 PM
 

Ian Yates said:

I know this is a really old post, but I happened to come to this from the sp_blitz pages  (I only discovered them last night).  Lo and behold, today SQL 2012 SP1 is out and they have changed the security requirements in this area for the better.  In case anyone stumbles on this old post please refer to http://msdn.microsoft.com/en-us/library/bb500435  (What's new in SQL Server 2012 - updated for SP1) and look for the heading "DBCC SHOW_STATISTICS works with SELECT permission".  It calls out distributed/linked servers specifically :)  YAY

November 7, 2012 9:14 PM
 

Natesh said:

Hi,

I've the same problem with my DBs on SQL Server 2005.  The solution provided above is applicable to SQL Server 2005 too?

Regards,

Natesh

November 8, 2012 6:15 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement