Allow statistics to be read if data can be read -- performance and security - by DWalker59

Status : 

 


39
0
Sign in
to vote
ID 626262 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 12/1/2010 8:27:23 AM
Access Restriction Public

Description

For SQL 2008, Books Online has been revised to say the following (per Connect item 476001):

"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 serveris 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_ownerfixed database role, or the db_ddladmin fixed database role on the linkedserver."

This is a poor solution from a security standpoint.
Sign in to post a comment.
Posted by mailtovishal on 7/14/2013 at 11:57 AM
In a clustered environment is it better to use ssis package or stored procedures using linked server for data transfer between two instances
Posted by SAinCA on 12/17/2012 at 11:33 AM
For SQL2012 Users, please see the augmented note at http://msdn.microsoft.com/en-us/library/ms189811.aspx, which states, "...SQL Server 2012 SP1 modifies the permission restrictions for obtaining statistics and allows users with SELECT permission to access statistics available through DBCC SHOW_STATISTICS. For more information, see the Permissions section of DBCC SHOW_STATISTICS (Transact-SQL)."

The answer @CraigIW2 is a hearty, "YES!"

This adds more justification to upgrading to SQL2012 from SQL2005, bypassing 2008R2 as this change has NOT been back-ported.
Posted by Ellen2 on 11/7/2012 at 1:15 PM
Has this issue been "fixed" in SQL Server 2008? Permissions to read/use statistics on a table that the user has permissions to select from should be automatic -- whether the user connects via a linked server connection or is directly connected.

A user that qualifies as "read only" should not have to be granted sysadmin or db_owner permissions to retrieve data efficiently.

Posted by dm_ntm on 10/4/2012 at 7:14 AM
Microsoft, please provide an update. This is killing us from a security standpoint.
Posted by CraigIW2 on 2/13/2012 at 4:06 AM
@Microsoft. It is now a year later. Is this fixed in SQL 2012?
Posted by ChrisAVWood on 3/24/2011 at 7:52 AM
Adding a new role sounds like a good idea rather than use as much broader existing role like db_datareader.

Chris
Posted by Microsoft on 1/17/2011 at 8:20 AM
Hi,

Thanks for your suggestion. We will consider it and respond shortly.
Posted by DWalker59 on 12/2/2010 at 10:08 AM
OK, I changed the title and the proposed fix. So my proposed fix now matches what I said in the previous comment just below this one.
Posted by DWalker59 on 12/2/2010 at 7:59 AM
After spending a fvew hours thinking about my own Connect entry here, I think my proposed fix was not the best.

The PROBLEM is that having the authority to read the data doesn't give you the authority to read the statistics. Isn't this a poor design?

I would gladly accept a change where anyone who can read the data, can read the statistics. SQL should not require roles like Sysadmin, db_Ownerfixed, or db_ddladminfix in order to READ statistics. All of those roles allow changes to the data and changes to the tables.

Anyone with the db_Datareader role should be allowed to read the statistics. Allowing users who have the db_datareader role to also read statistics, is NOT a lessengin of security. Anyone who can read the data could generate the statistics if they wanted to.

If SQL is allowing you to read the data, shouldn't SQL let you read the data in the most efficient way? That is, while also letting you read the statistics?
Posted by DWalker59 on 12/1/2010 at 9:18 AM
The proposed solution (hidden by default) is to create a role that only allows statistics to be read.