What are the possible disadvantages of enabling the "data access" server option in sys.servers for t
Posted
by Corp. Hicks
on Server Fault
See other posts from Server Fault
or by Corp. Hicks
Published on 2010-05-27T12:23:57Z
Indexed on
2010/05/28
8:34 UTC
Read the original article
Hit count: 329
We plan to change the default server options of an SQL2k5 server instance by enabling data access.
The reason is that we want to run "SELECT * FROM OPENQUERY(LOCALSERVER, '...')" -like statements on the server.
What are the possible disadvantages of enabling server option "data access" (alias sys.servers.is_data_access_enabled) for the local server (sys.servers.server_id = 0)?
(There must be a reason for MS setting this option to disabled by default...)
EDIT: it turns out that I'm not the first person to ask this question:
"The DATA ACCESS server option is not very well documented in my opinion - the Books On Line say it is a property of linked servers. It doesn't mention at all that you actually can have it enabled on your local server to enable OPENQUERY calls. I noticed that when you disable DATA ACCESS on a linked server, you can't query any table located on it (I tested it on my loopback server) neither using OPENQUERY nor four-part naming convention. You can still call procedures (with four-part naming) that return rowsets. Well, the interesting question is why it is disabled by default on local server - I suppose to discourage users from using OPENQUERY against it."
It also seems that the author of the post (Pjotr Rodak) is a Stack Overflow user :-)
© Server Fault or respective owner
