Over the recent months I have seen a deprecation of performance on some of the linked server queries. I know I should not be using them but sometimes it is a must. At the same time we had been making some security changes on the primary OLTP db. So the question must be asked, Does it matter what the linked server account permission access is as long as it has select on the tables in the case? Well apparently it does.
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.
Depending on the method (all records returned and join on the local or one row at a time) the linked server account will need access to the distribution statistics.
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.
Update: Apparently after reading more, this was resolved in Sql 2012. So if you are running a Sql 2008R2 or less this is a valid concern.
Refernce
0 comments:
Post a Comment