What are the possible disadvantages of enabling the "data access" server option in sys.servers for t
- by Corp. Hicks
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:
http://sqlblogcasts.com/blogs/piotr_rodak/archive/2009/11/22/data-access-setting-on-local-server.aspx
"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 :-)
